P04 · SQL & Querying
SQL GROUP BY and aggregates — the quiet miscounts
How SQL aggregates and GROUP BY really work — and the counting trap where COUNT skips the blank rows and a total quietly reads low.
Start with the basics
Aggregation is how you turn many rows into a summary — a count, a sum, an average. You pick the columns to group by, and the database collapses all the rows that share those values into a single row.
GROUP BY names the groups; the aggregate (COUNT, SUM, AVG) then answers a question about each group. Group orders by region and add up the amounts, and you get one total per region.
It reads like a spreadsheet pivot, and most of the time it behaves like one. The traps are all about what quietly gets left out of the count.
One that works
| region | amount |
|---|---|
| North | 100 |
| North | 50 |
| South | 200 |
SELECT region, SUM(amount) AS total
FROM sales
GROUP BY region
ORDER BY region;| region | total |
|---|---|
| North | 150 |
| South | 200 |
Each region collapses to one row; SUM adds up that group's amounts.
GROUP BY collapses rows into one row per group, and each aggregate summarizes that group.
When to use it
- Reach for GROUP BY whenever you want a per-category summary: sales per region, orders per customer, sign-ups per day.
- Use an aggregate on its own (no GROUP BY) for a single grand total across all rows.
- If you keep pulling every row to add them up in your app, that is a GROUP BY waiting to happen.
That is also where counting quietly lies. An aggregate is only as honest as what it decides to skip — and by default, counting a column skips the blanks. Here is the day that reads your total low.
See it happen
The total that reads low — COUNT skips blanks
You want the number of ballots cast. One voter left the choice blank. Watch what counting the choice column does with that blank row.
| voter | choice |
|---|---|
| 1 | A |
| 2 | NULL |
| 3 | A |
| 4 | B |
COUNT(choice) — skips the blank
SELECT COUNT(choice) AS n
FROM ballots;| n |
|---|
| 3 |
COUNT(*) — counts every row
SELECT COUNT(*) AS n
FROM ballots;| n |
|---|
| 4 |
The “ballots cast” total reads 3, not 4 — counting the choice column silently skipped the blank ballot.
Walk the full example in the unit →The rest of the family
More ways a GROUP BY goes quietly wrong
A sum over an empty group, or a group whose values are all missing, returns NULL rather than zero — wrap it so an empty group reads as 0.
A row filter runs before grouping and a group filter runs after, so putting a group total in the row filter (or vice versa) filters the wrong thing.
A column that is neither grouped nor summarized errors in strict databases and returns silent garbage in loose ones.
GROUP BY collects all the rows with a missing key into one group rather than skipping them.
Dividing one integer total by another drops the fraction unless you convert to a decimal first.
Averaging each group's average weights every group equally regardless of size, so it does not equal the overall average — and nothing errors.
Concatenating a group's values into one string without an explicit order stitches them in whatever order the scan produced.
Self-check
How to spot it in your own SQL
- A total or count looks lower than the number of rows you can actually see.
- You are counting a column that is allowed to be blank or missing.
- You wrote a filter and are not sure whether it belongs before or after GROUP BY.
- An average across groups of very different sizes looks surprising.
What people miss
Aggregation feels like arithmetic, but the traps are all about what silently does not get counted — a blank ballot, an empty group, an average of averages. Nothing errors; the number just reads wrong, and every dashboard built on it inherits the error.
Go deeper
Ask Colearn
Instant answers, grounded in the same verified material the diagnostic grades against.
Why is my COUNT lower than the number of rows?
Counting a specific column skips the rows where that column is blank — it counts only the non-missing values. Counting the rows themselves counts every row regardless.
For a row total, count the rows (COUNT(*)); count a column only when you specifically want its non-blank values.
from the unit →Why does my SUM return NULL instead of 0?
A sum over no rows, or a group whose values are all missing, returns NULL — not zero. Downstream math on that NULL then spreads the emptiness.
Wrap the sum so it falls back to 0 when there is nothing to add.
from the unit →Should my filter go in WHERE or HAVING?
WHERE runs before grouping, so it filters individual rows. HAVING runs after, so it filters whole groups by their totals.
A test on a single row belongs in WHERE; a test on a group's total (like “more than 10 orders”) belongs in HAVING.
from the unit →Why does my average look off across groups?
If you average each group's average, every group counts equally no matter how many rows it has — so a tiny group sways the result as much as a huge one.
For the true overall average, compute it from the underlying totals, not from the per-group averages.
from the unit →That’s the free taste