How to group and average rows by hand before you write SQL

A GROUP BY query looks like one step, but it's really two: first sort every row into a bucket by some key, then run one aggregate function — SUM, AVG, COUNT, MIN, MAX — over the rows in each bucket, separately. Once you can do that sort-then-reduce by hand on paper, the SQL is just a shorthand for a process you already understand.

The method

1. Pick the grouping column — the value you want one result row per distinct value of.
2. Walk the rows once, putting each one into a bucket named by its grouping-column value. Order inside a bucket doesn't matter.
3. For each bucket, run the aggregate: add up the numbers for SUM, divide the sum by the row count for AVG, or just count the rows for COUNT.
4. Write one output row per bucket: the grouping value plus its aggregate result.
5. HAVING is the same filter as WHERE, just applied after the buckets exist — it checks the aggregate result, not a raw row, which is why you can't put an aggregate condition in WHERE.

A worked example

Five orders, each with a region and an amount:

• North: $120
• South: $150
• North: $80
• South: $90
• North: $200

Step 1–2: sort into buckets by region. North gets [120, 80, 200]; South gets [150, 90].

Step 3–4: North's sum is 120 + 80 + 200 = 400, over 3 rows, so its average is 400 ÷ 3 = 133.33. South's sum is 150 + 90 = 240, over 2 rows, so its average is 240 ÷ 2 = 120.

The output has exactly one row per bucket:

• North — count 3, sum 400, avg 133.33
• South — count 2, sum 240, avg 120

That's the entire result of SELECT region, COUNT(*), SUM(amount), AVG(amount) FROM orders GROUP BY region. Note the grand total across both buckets, 400 + 240 = 640, never appears anywhere in the output — grouping replaces the whole table with one row per bucket, it doesn't add a row underneath it.

Where this catches real mistakes

The classic error is asking for a column that isn't the grouping key and isn't wrapped in an aggregate — for example SELECT region, amount FROM orders GROUP BY region. Which amount would print for North: 120, 80, or 200? There's no single answer, because the bucket holds three rows, not one, so most databases reject the query outright. The fix is always the same: either aggregate that column (SUM(amount), MAX(amount)) or add it to the GROUP BY list, which just makes the buckets finer.

The second mistake is reaching for WHERE AVG(amount) > 100 to filter on an average. WHERE runs before buckets exist, row by row, so it has no average to test yet. HAVING AVG(amount) > 100 runs after the buckets and their aggregates are computed, which is the only point at which "the average of this bucket" is a value that exists to compare.

The free SQL Playground lets you paste a CSV, have it become a table called t, and run real GROUP BY, HAVING and JOIN queries against it entirely in your browser — nothing uploaded, so it's safe on data you'd never paste into an online SQL sandbox. Because the rows are yours, you already know what the bucket totals should be, which makes a wrong query obvious instead of a mystery.