{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"the-grain-of-a-result","title":"Grain: the sentence to say before you write the query","url":"https://addaly.com/learn/data-and-sql/the-grain-of-a-result","position":10,"of":66,"minutes":8,"answer":"Say \"one row of my result is one ___\" before writing the query; every aggregate reads the joined grain, not the original table's, so a join that changes the grain is the whole hazard.","keyIdea":"Say \"one row of my result is one ___\" before writing the query; every aggregate reads the joined grain, not the original table's, so a join that changes the grain is the whole hazard."},"body":{"format":"markdown","text":"## One sentence, and most wrong answers do not happen\n\nBefore writing any query, finish this sentence out loud:\n\n> **One row of my result is one ____.**\n\nOne customer. One order. One customer-month. One payment attempt. If you cannot finish it, you are not ready to write the query, and the number you produce will be wrong in a way that does not look wrong.\n\nThat sentence names the **grain** of your result. Grain is the most useful idea in this course and almost nobody is taught it explicitly.\n\n## Every table has a grain too\n\nBefore combining tables you need to know the grain of each. It is a fact you can measure, not a matter of opinion:\n\n```sql\nSELECT count(*) AS rows,\n       count(DISTINCT order_id) AS orders\nFROM order_items;\n-- rows 84,201 | orders 31,940  → one row per line item\n```\n\nNames lie. A table called `daily_metrics` may hold one row per day per country per product, which is not daily anything. A table called `users` may hold one row per user per sign-up source, so a user who arrived twice appears twice. Measure it once; write the answer down.\n\n## Joining changes the grain, and that is the entire hazard\n\nHere is the mechanism stated as a rule you can apply without thinking:\n\n**Joining a table at grain A to a table at grain B gives you grain B, whenever many B rows belong to one A row.**\n\nSo joining `customers` (one row per customer) to `orders` (one row per order) gives you one row per *order*, with customer details repeated. Every customer-level number in that result (credit limit, signup date, lifetime value) is now duplicated once per order and cannot be summed. The joins lesson shows exactly what that does to a SUM.\n\nYou have three moves, and picking the right one is the skill:\n\n1. **Aggregate the many side first**, then join. One row per customer stays one row per customer.\n2. **Join, then group back up.** Works, but any customer-level column you SUM after joining is wrong; only COUNT and the aggregates over the many side are safe.\n3. **Use a scalar subquery or a lateral join** to attach one summarised value per row without changing grain at all.\n\n```sql\n-- Option 1: aggregate first, grain stays one row per customer\nWITH per_customer AS (\n  SELECT customer_id, sum(amount) AS spend, count(*) AS orders\n  FROM orders GROUP BY customer_id\n)\nSELECT c.id, c.full_name, c.credit_limit,\n       coalesce(p.spend, 0) AS spend\nFROM customers c\nLEFT JOIN per_customer p ON p.customer_id = c.id;\n```\n\nRead the grain at each step. The CTE is one row per customer. `customers` is one row per customer. Joining one-to-one preserves it. `sum(credit_limit)` over that result is now correct, and it was not correct before.\n\n## The grain of a report is a business decision\n\n\"Revenue by month\" sounds unambiguous until you ask what a month is a property of. Order date? Payment settlement date? The month the subscription covers? Three different grains, three different tables, three different numbers, all defensible.\n\nSimilarly \"one row per customer per month\" needs a decision about customers with no activity that month. Do they appear with zero, or not appear at all? A grid that only contains rows where something happened will show a gap where a real zero should be, and the chart drawn from it will interpolate straight across the gap.\n\nState the grain in words when you send the number:\n\n> Figure: The grain changes at the join, and every aggregate reads the new one. Layers, top to bottom: customers (one row per customer); orders (one row per order); customers JOIN orders (one row per order; the customer's columns repeat); sum(credit_limit) over that result (each limit counted once per order: wrong); Aggregate orders first, then join one to one (one row per customer, and the sum is right). Read the grain at each step. A customer-level column summed after a one-to-many join is inflated once per order, and nothing in the output shows it. Aggregating the many side first keeps the grain, and only then is sum(credit_limit) the number it looks like.\n\n> One row is one customer-month, for every customer who existed at the start of the month, including months with no orders (shown as zero), by order placement date in Asia/Kolkata.\n\nNobody enjoys writing that sentence. It has ended more disagreements than any query I have seen.\n\n## A check you can run\n\nAfter writing any query, before believing it, compare the row count against the grain you claimed:\n\n```sql\nSELECT count(*), count(DISTINCT customer_id) FROM my_result;\n```\n\nClaimed one row per customer? Those two numbers must match. If they do not, a join multiplied and every per-customer aggregate is inflated by an amount you cannot see in the output.\n\nThis takes eight seconds and it catches the majority of the errors that make it into meetings. It is the cheapest quality check in the whole of data work, and the reason it is not universal is that most people were never taught to name the grain in the first place."},"check":{"question":"A query joins `customers` to `orders` on `customer_id` and, in one pass, computes `sum(orders.amount)` and `sum(customers.credit_limit)`. Which of the two totals can be trusted?","options":[{"text":"Both totals are correct, because each SUM only reads its own table's column.","correct":false,"tempting":"The SUM reads the joined result, not the original table; the join has already duplicated the customer rows before any aggregation happens."},{"text":"The order total is right and the credit-limit total is inflated, because the join repeated each customer's limit on every one of their orders.","correct":true},{"text":"Both totals are inflated, because a join multiplies rows on both sides of the relationship, and every SUM computed after it will therefore double-count in proportion.","correct":false,"tempting":"Only the one side is duplicated; each order still appears exactly once, so the order total is unaffected."},{"text":"Neither total can be trusted until DISTINCT is added to both aggregates.","correct":false,"tempting":"`SUM(DISTINCT ...)` would drop genuinely repeated values (two customers with the same limit, two orders of the same amount) replacing one error with another."}]},"prev":{"slug":"reading-an-unfamiliar-schema","title":"Reading a database somebody else built","url":"https://addaly.com/learn/data-and-sql/reading-an-unfamiliar-schema"},"next":{"slug":"right-vs-useful","title":"A right number and a useful number are not the same","url":"https://addaly.com/learn/data-and-sql/right-vs-useful"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'Grain: the sentence to say before you write the query', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/the-grain-of-a-result (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"Grain: the sentence to say before you write the query","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":1,"slug":"tables-types-and-the-question","title":"Tables, types and the question you are actually asking"},"answer":null,"keyIdea":"Say “one row of my result is one ___” before writing the query; every aggregate reads the joined grain, not the original table’s, so a join that changes the grain is the whole hazard.","hero":{"kind":"figure","figure":{"t":"figure","label":"Figure","figure":{"type":"stack","title":"The grain changes at the join, and every aggregate reads the new one","layers":[{"label":"customers","detail":"one row per customer"},{"label":"orders","detail":"one row per order"},{"label":"customers JOIN orders","detail":"one row per order; the customer’s columns repeat"},{"label":"sum(credit_limit) over that result","detail":"each limit counted once per order: wrong"},{"label":"Aggregate orders first, then join one to one","detail":"one row per customer, and the sum is right"}],"caption":"Read the grain at each step. A customer-level column summed after a one-to-many join is inflated once per order, and nothing in the output shows it. Aggregating the many side first keeps the grain, and only then is sum(credit_limit) the number it looks like."},"alt":"Figure: The grain changes at the join, and every aggregate reads the new one. Layers, top to bottom: customers (one row per customer); orders (one row per order); customers JOIN orders (one row per order; the customer’s columns repeat); sum(credit_limit) over that result (each limit counted once per order: wrong); Aggregate orders first, then join one to one (one row per customer, and the sum is right). Read the grain at each step. A customer-level column summed after a one-to-many join is inflated once per order, and nothing in the output shows it. Aggregating the many side first keeps the grain, and only then is sum(credit_limit) the number it looks like."}},"blocks":[{"t":"h2","id":"one-sentence-and-most-wrong-answers-do-not-happen","runs":[{"text":"One sentence, and most wrong answers do not happen"}]},{"t":"p","runs":[{"text":"Before writing any query, finish this sentence out loud:"}]},{"t":"quote","runs":[{"text":"One row of my result is one ____.","b":true}]},{"t":"p","runs":[{"text":"One customer. One order. One customer-month. One payment attempt. If you cannot finish it, you are not ready to write the query, and the number you produce will be wrong in a way that does not look wrong."}]},{"t":"p","runs":[{"text":"That sentence names the "},{"text":"grain","b":true},{"text":" of your result. Grain is the most useful idea in this course and almost nobody is taught it explicitly."}]},{"t":"h2","id":"every-table-has-a-grain-too","runs":[{"text":"Every table has a grain too"}]},{"t":"p","runs":[{"text":"Before combining tables you need to know the grain of each. It is a fact you can measure, not a matter of opinion:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT count(*) AS rows,\n       count(DISTINCT order_id) AS orders\nFROM order_items;\n-- rows 84,201 | orders 31,940  → one row per line item"},{"t":"p","runs":[{"text":"Names lie. A table called "},{"text":"daily_metrics","code":true},{"text":" may hold one row per day per country per product, which is not daily anything. A table called "},{"text":"users","code":true},{"text":" may hold one row per user per sign-up source, so a user who arrived twice appears twice. Measure it once; write the answer down."}]},{"t":"h2","id":"joining-changes-the-grain-and-that-is-the-entire-hazard","runs":[{"text":"Joining changes the grain, and that is the entire hazard"}]},{"t":"p","runs":[{"text":"Here is the mechanism stated as a rule you can apply without thinking:"}]},{"t":"p","runs":[{"text":"Joining a table at grain A to a table at grain B gives you grain B, whenever many B rows belong to one A row.","b":true}]},{"t":"p","runs":[{"text":"So joining "},{"text":"customers","code":true},{"text":" (one row per customer) to "},{"text":"orders","code":true},{"text":" (one row per order) gives you one row per "},{"text":"order","i":true},{"text":", with customer details repeated. Every customer-level number in that result (credit limit, signup date, lifetime value) is now duplicated once per order and cannot be summed. The joins lesson shows exactly what that does to a SUM."}]},{"t":"p","runs":[{"text":"You have three moves, and picking the right one is the skill:"}]},{"t":"ol","start":1,"items":[[{"text":"Aggregate the many side first","b":true},{"text":", then join. One row per customer stays one row per customer."}],[{"text":"Join, then group back up.","b":true},{"text":" Works, but any customer-level column you SUM after joining is wrong; only COUNT and the aggregates over the many side are safe."}],[{"text":"Use a scalar subquery or a lateral join","b":true},{"text":" to attach one summarised value per row without changing grain at all."}]]},{"t":"code","lang":"sql","label":"SQL","text":"-- Option 1: aggregate first, grain stays one row per customer\nWITH per_customer AS (\n  SELECT customer_id, sum(amount) AS spend, count(*) AS orders\n  FROM orders GROUP BY customer_id\n)\nSELECT c.id, c.full_name, c.credit_limit,\n       coalesce(p.spend, 0) AS spend\nFROM customers c\nLEFT JOIN per_customer p ON p.customer_id = c.id;"},{"t":"p","runs":[{"text":"Read the grain at each step. The CTE is one row per customer. "},{"text":"customers","code":true},{"text":" is one row per customer. Joining one-to-one preserves it. "},{"text":"sum(credit_limit)","code":true},{"text":" over that result is now correct, and it was not correct before."}]},{"t":"h2","id":"the-grain-of-a-report-is-a-business-decision","runs":[{"text":"The grain of a report is a business decision"}]},{"t":"p","runs":[{"text":"“Revenue by month” sounds unambiguous until you ask what a month is a property of. Order date? Payment settlement date? The month the subscription covers? Three different grains, three different tables, three different numbers, all defensible."}]},{"t":"p","runs":[{"text":"Similarly “one row per customer per month” needs a decision about customers with no activity that month. Do they appear with zero, or not appear at all? A grid that only contains rows where something happened will show a gap where a real zero should be, and the chart drawn from it will interpolate straight across the gap."}]},{"t":"p","runs":[{"text":"State the grain in words when you send the number:"}]},{"t":"quote","runs":[{"text":"One row is one customer-month, for every customer who existed at the start of the month, including months with no orders (shown as zero), by order placement date in Asia/Kolkata."}]},{"t":"p","runs":[{"text":"Nobody enjoys writing that sentence. It has ended more disagreements than any query I have seen."}]},{"t":"h2","id":"a-check-you-can-run","runs":[{"text":"A check you can run"}]},{"t":"p","runs":[{"text":"After writing any query, before believing it, compare the row count against the grain you claimed:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT count(*), count(DISTINCT customer_id) FROM my_result;"},{"t":"p","runs":[{"text":"Claimed one row per customer? Those two numbers must match. If they do not, a join multiplied and every per-customer aggregate is inflated by an amount you cannot see in the output."}]},{"t":"p","runs":[{"text":"This takes eight seconds and it catches the majority of the errors that make it into meetings. It is the cheapest quality check in the whole of data work, and the reason it is not universal is that most people were never taught to name the grain in the first place."}]}],"check":{"question":[{"text":"A query joins "},{"text":"customers","code":true},{"text":" to "},{"text":"orders","code":true},{"text":" on "},{"text":"customer_id","code":true},{"text":" and, in one pass, computes "},{"text":"sum(orders.amount)","code":true},{"text":" and "},{"text":"sum(customers.credit_limit)","code":true},{"text":". Which of the two totals can be trusted?"}],"options":[{"text":[{"text":"Both totals are correct, because each SUM only reads its own table’s column."}],"correct":false,"tempting":[{"text":"The SUM reads the joined result, not the original table; the join has already duplicated the customer rows before any aggregation happens."}]},{"text":[{"text":"The order total is right and the credit-limit total is inflated, because the join repeated each customer’s limit on every one of their orders."}],"correct":true,"tempting":null},{"text":[{"text":"Both totals are inflated, because a join multiplies rows on both sides of the relationship, and every SUM computed after it will therefore double-count in proportion."}],"correct":false,"tempting":[{"text":"Only the one side is duplicated; each order still appears exactly once, so the order total is unaffected."}]},{"text":[{"text":"Neither total can be trusted until DISTINCT is added to both aggregates."}],"correct":false,"tempting":[{"text":"SUM(DISTINCT ...)","code":true},{"text":" would drop genuinely repeated values (two customers with the same limit, two orders of the same amount) replacing one error with another."}]}],"order":[2,1,3,0],"why":null},"prev":{"slug":"reading-an-unfamiliar-schema","title":"Reading a database somebody else built","minutes":9},"next":{"slug":"right-vs-useful","title":"A right number and a useful number are not the same","minutes":9}}}