SQL row grain: stop double-counting orders before you query

A SQL row's grain is the real-world thing that one row represents. An order table may contain one row per order; an order-item table contains one row per item line. Joining those tables changes the rows you are adding. If you sum an order-level amount after expanding it to item-level rows, you can count the same money several times.

This is why a query can run without errors, produce a sensible-looking dashboard and still be wrong. SQL checks whether the operation is valid. It does not know which business quantity you intended to measure.

We will reproduce the problem with eight completed orders, then fix it in two ways. The commerce SQL lab contains original synthetic CSV files, an executable Python/SQLite runner and reference results. No database server or real customer data is required.

State the metric before joining anything

Our question is: What is the recorded order value of completed orders, before refunds?

The contract is deliberately precise:

• Count each eligible order once.
• Include only orders whose status is completed.
• Use order_total_paise, already recorded after any discount.
• Report refunds separately; do not subtract them from this metric.
• Retain an order even if its customer record is missing, and flag that issue separately.

Amounts are integer paise. A result of 104,000 paise means ₹1,040. This is an exercise metric, not an accounting policy for recognizing revenue.

The relevant grains are:

order_id is unique in the first table and intentionally repeated in the second. Two item lines do not mean two orders.

Establish a reference total

Start with the table that already has the grain your question requires:

SELECT
    COUNT(*) AS completed_orders,
    SUM(order_total_paise) AS completed_value_paise
FROM orders
WHERE status = 'completed';

The verified result is eight orders and 104,000 paise. Save both values. A row count is a useful companion to a monetary total because it can reveal how a later join changes the population.

Now suppose someone adds item details to investigate product performance:

SELECT
    COUNT(*) AS joined_rows,
    SUM(o.order_total_paise) AS reported_value_paise
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.order_id
WHERE o.status = 'completed';

The query returns 12 joined rows and 171,000 paise. The business did not earn extra money. Several order headers were repeated once for each matching item line.

Order O1001 makes the mechanism visible. Its header contains 12,000 paise, and it has two item lines. After the join, 12,000 appears twice. Adding that header column contributes 24,000, although the order is still worth 12,000.

The same problem can arise with shipments, payments, support tickets or refund events. It is a relationship problem, not a special property of sales tables.

Fix the calculation at the appropriate grain

If you only need total order value, use the original order-level query. A join that contributes no required information adds risk without helping answer the question.

If you genuinely need a product-level result, use an amount defined at item-line grain:

SELECT SUM(i.line_total_paise) AS completed_value_paise
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.order_id
WHERE o.status = 'completed';

This returns 104,000 paise. In this fixture, item totals already include their allocated discounts and reconcile with the header. You must verify that assumption in a real system. Shipping, tax or order-level discounts may be stored only on the header, so line totals are not automatically interchangeable with order totals.

When you need item statistics next to each order, aggregate the many-side first:

WITH item_summary AS (
    SELECT order_id, COUNT(*) AS line_count
    FROM order_items
    GROUP BY order_id
)
SELECT o.order_id, o.order_total_paise, i.line_count
FROM orders AS o
LEFT JOIN item_summary AS i ON i.order_id = o.order_id
WHERE o.status = 'completed';

item_summary has at most one row per order. That preserves the order grain. The LEFT JOIN also keeps an eligible order if its item records are unexpectedly absent; missing line counts can then be investigated rather than silently excluded.

Why DISTINCT is not a general repair

SUM(DISTINCT order_total_paise) deduplicates amount values, not orders. Different orders can legitimately have the same value. In our fixture, two orders are worth 12,000 paise and two are worth 10,000. A distinct sum produces 82,000 paise, which is another incorrect answer.

The repair is to define the entity and its key. It is not to remove repeated-looking numbers until a total appears plausible. SQLite documents joins and distinct processing separately; understanding that distinction makes these errors easier to diagnose. SQLite SELECT reference.

Make reconciliation part of the deliverable

Run the lab verifier:

python 11-Blog-Programme/labs/commerce-sql/build_and_verify.py

It checks the correct total, the deliberately inflated total and the incorrect distinct sum. It also changes one header amount and confirms that an independent line-to-header reconciliation detects the mismatch.

For your own analysis, write down the input grain, output grain, join cardinality and expected population. Check unique keys before the join, unmatched records after it, and at least one independently calculated control total. A successful query execution is only the beginning of validation.

Practice: add a third item line to an order while keeping the header unchanged. Decide whether the item total should be reallocated or whether the new line is a data error. Your answer should depend on the metric contract, not on whichever number your query happens to return.

NeuraPath's Data Analytics with Generative AI programme covers the SQL, data-wrangling and dashboard foundations needed to carry this reasoning across an analytical pipeline. The useful skill is being able to explain why a number is trustworthy before using it in a decision.

Continue learning

This article is part of the SQL foundations for reliable analysis sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Continue with SELECT and WHERE: build a reproducible sales extract.
• Then apply it in SQL NULL values: why missing is not zero.