Grouping and summing are some of the most useful tools in data analysis. They are also easy to apply before checking whether the underlying rows mean what we think they mean. A query can run correctly and still produce a misleading result if the row grain or identifiers are unclear.
Start with the row grain
The grain describes what a single row represents: for example, one order, one order line, or one customer per month. A transaction table often has several rows for one order because each row represents a product line. That can be perfectly valid—but it should be clear before interpreting counts and averages.
If the grain is one order line, then counting rows measures lines, not orders. Summing line amounts may give a total across lines, while averaging those amounts gives an average line value. Neither should be given an order-level label without additional steps.
Check whether the keys agree
In a synthetic retail SQL exercise I reviewed, the same
order_id appeared on multiple rows with different
customer_id and order_date values. For
example, ORD00001 appeared four times, but the
customer and date values did not stay consistent. That makes it
unsafe to assume those rows are valid lines from one real order.
A diagnostic query can test that assumption by grouping on the order identifier and looking for more than one customer or date. Such a check identifies records to investigate; it does not repair them or prove which value is correct.
Labels should match the calculation
Consider COUNT(DISTINCT order_id). It counts distinct
identifier values. If those identifiers are inconsistent, that
number is not automatically a trustworthy count of orders.
Similarly, AVG(sales_amount) over order-line rows is
an average row amount. A true average order value would require
first calculating a total for each valid order, then averaging
those order totals. If the order key is unreliable, that
order-level calculation is not reliable either.
A practical sequence
- Write down what one row is intended to represent.
- Check whether key fields are missing or have conflicting values.
- Define each metric in terms of that row grain.
- Only then group, aggregate, and label the result.
- Keep unresolved data limitations beside the reported figures.
What this means for interpretation
This lesson came from an independent exercise using synthetic retail data, not from work for a retailer. Its row-level summaries demonstrate SQL techniques, but the inconsistent identifiers mean its order-level, customer-level, and time-based conclusions should not be treated as dependable business findings.
The broader lesson is useful beyond this example: data quality is not a separate box to tick after analysis. Understanding grain and keys is part of defining the question and choosing a calculation that can answer it.
Key takeaways
- Know what one row represents before counting or averaging.
- Check that identifiers behave consistently across related rows.
- Make metric labels describe the calculation that was actually performed.
- Do not turn uncertain aggregates into confident business claims.