How SQL GROUP BY works: aggregates, COUNT and WHERE vs HAVING

The idea

An aggregate function takes many rows and returns one value: a count, a sum, an average, a smallest or a largest. SELECT COUNT(*) FROM orders turns twelve thousand rows into one row that says 12000.

GROUP BY does the same thing once per group instead of once for the whole table. It sorts the rows into piles by the value of a column, such as channel, and then each pile is folded into a single row. Eight orders in, three rows out. The detail is gone on purpose, so you can see the pattern.

Figure 1

GROUP BY channel folds eight orders into three rows

orders
order_id	channel	status
1003	marketplace	completed
1004	marketplace	completed
1005	web	completed
1006	marketplace	cancelled
1007	marketplace	completed
1008	web	completed
1009	app	completed
1010	web	completed

COUNT(*) per channel
channel	orders
marketplace	4
web	3
app	1

Step 1 of 4 Eight orders, one row each. The question: how many orders per channel? That is SELECT channel, COUNT(*) FROM orders GROUP BY channel.

Eight orders, one row each. The question: how many orders per channel? That is SELECT channel, COUNT(*) FROM orders GROUP BY channel.
First GROUP BY sorts the rows into piles, one pile per distinct channel. The four marketplace orders go into the first pile.
Then the aggregate runs once per pile. Each pile becomes one result row. The three web orders fold into one row that says 3.
The app pile has a single order, so it folds into a row that says 1. Eight rows in, three rows out. The individual orders are gone: only the group and its count remain.

Why it exists

Nobody in a meeting wants a list of 12,000 orders. They want to know how many per channel, revenue per month, the average order per customer. Those are summaries, and the whole point is to lose the detail in a controlled way. Without GROUP BY you would filter and count by hand, once per channel, and write a new query whenever a new channel appeared. With it, one query handles every group, including ones you didn't know existed. The cost is that a summary can hide things: an average of 40 dollars may come from forty orders of 40 or from one of 1,000 and many tiny ones. Look at a count next to every average, so you know how many rows stand behind it.

How it works under the hood

The query runs in a fixed order, and the order explains every rule of this chapter:

FROM picks the table.
WHERE keeps or drops individual rows.
GROUP BY sorts the surviving rows into piles by the key.
Aggregates (COUNT, SUM, AVG, MIN, MAX) compute one value per pile.
HAVING keeps or drops whole piles, using the aggregate values.
SELECT, ORDER BY and LIMIT shape and cut what is left.

WHERE and HAVING

Both filter, but at different moments and about different things. WHERE talks about a row (a status, a channel). HAVING talks about a group (its COUNT(*), its SUM). A condition on a total cannot go in WHERE, because totals do not exist yet when WHERE runs.

Figure 2

WHERE removes rows before the groups exist, HAVING removes groups after

FROM orders 8 orders
WHERE status = 'completed' drops order 1006, a row
GROUP BY channel marketplace 3, web 3, app 1
HAVING COUNT(*) >= 3 drops app, a group
SELECT channel, COUNT(*) marketplace 3, web 3

Step 1 of 5 All eight orders enter. Nothing has been grouped yet, so there is no count to test.

All eight orders enter. Nothing has been grouped yet, so there is no count to test.
WHERE looks at single rows. The cancelled order 1006 fails and leaves, so marketplace will only count 3 orders, not 4.
Now the rows are folded into groups: marketplace 3, web 3 and app 1. Only here do the counts exist.
HAVING looks at whole groups, using the aggregate. The app group has 1 order, so the whole group is dropped.
SELECT shows what is left: two groups, two rows. WHERE COUNT(*) >= 3 would fail instead, because at the WHERE step no count exists.

A useful test: if the condition mentions an aggregate, it belongs in HAVING. If it describes a single row, put it in WHERE. Row conditions in WHERE are also faster, because they shrink the data before the grouping work starts.

What SELECT may contain

After grouping, there is one row per group, so every column in SELECT must be one of two things: the grouping key itself, or an aggregate. SELECT channel, status, COUNT(*) ... GROUP BY channel is ambiguous: a channel has several statuses, and the database does not know which to show you. Either add status to GROUP BY, which makes smaller groups, or leave it out.

Counting: three versions

COUNT(*) counts rows. COUNT(column) counts the rows where that column is not NULL. COUNT(DISTINCT column) counts the different non-NULL values. All the other aggregates skip NULLs as well: AVG(unit_cost) averages only the products that have a cost.

Figure 3

COUNT(*) counts rows, COUNT(column) counts values that are not NULL

customers
customer_id	first_name	referral_source
1	Owen	search
2	Douglas	NULL
3	Henry	NULL
4	Brian	pinterest
5	Kyle	NULL

Result
count_star	count_referral
5	2

Step 1 of 3 Five customers. Three have no referral_source, which is NULL.

Five customers. Three have no referral_source, which is NULL.
COUNT(*) counts rows. It does not look inside them, so the answer is 5.
COUNT(referral_source) counts only the rows where that column has a value. The three NULLs are skipped and the answer is 2.

That is why "how many customers came from a referral?" and "how many customers are there?" are different questions with different counts, and why you should say which count you want.

Checking a grouped result

Add up the group counts: 4 + 3 + 1 is 8, the same as the eight rows you started with. If the numbers you get from a GROUP BY do not add up to the plain COUNT(*) of the filtered table, something was dropped: usually a NULL key. Rows with a NULL in the grouping column are not discarded. They form a group of their own, shown as an empty or NULL key, so look for it in the output before you trust the totals.

Ratios and rates

Many business numbers are one aggregate divided by another: refunds over orders, completed orders over all orders. Compute both in the same query and divide them, remembering that integer division can round to zero in some databases. Multiply by 1.0 first, or cast one side to a decimal. A rate computed per group is the usual shape: the group is the channel, and both numbers are folded from the same pile.

Revenue from order lines

Revenue is not stored per order. It is the sum of quantity times price over the lines of each order, so a revenue query sums an expression: SUM(quantity * unit_price). Aggregates take expressions, not just column names. Check that you sum at the right level. If you join first and then sum a column that belongs to the order, every line repeats it.

Common misconceptions

"COUNT(column) and COUNT(*) are the same"

They only agree when the column has no NULLs. In Harbor & Pine, COUNT(referral_source) is about two thirds of COUNT(*) for customers, because many customers have no referral source. Choose on purpose, and for "how many rows" use COUNT(*).

"WHERE and HAVING are interchangeable"

HAVING with a row condition sometimes works, but it filters after the grouping work is done and it is easy to misread. And a total in WHERE is an error. Think about when each runs and the choice makes itself. Putting a row condition in the wrong place can quietly change what is counted.

"GROUP BY sorts the result"

It sorts rows into piles internally, but the order of the output groups is not promised. If you want the biggest channel first, add ORDER BY COUNT(*) DESC. Without it, the same query can list the channels in a different order tomorrow.

Check yourself

One question. Answering it ticks this lesson off on the map; it never locks a level.

For orders 1003 to 1010 you run WHERE status = 'completed' GROUP BY channel HAVING COUNT(*) >= 4. Marketplace has 4 orders, one of them (1006) cancelled. What happens to marketplace? It stays with 4, because HAVING counts the groups before WHERE removes anything It disappears: WHERE removes 1006 first, the group has 3 orders, and 3 fails the HAVING It stays with 3, because HAVING only checks that the group exists The query fails, because WHERE and HAVING can't be in the same query