Grouping, and the two ways to count

COUNT(*) counts rows. COUNT(col) counts values. They are different numbers, and knowing which one you asked for is most of the skill.

12 min · runs against the shop dataset

GROUP BY collapses many rows into one per distinct key, and every column in the select list then has to be either one of those keys or something aggregated across the group. The error you get for breaking that rule is the most useful message in the language, because it tells you both fixes at once.

run it

SELECT c.city, count(*) AS orders, count(o.total) AS priced, sum(o.total) AS spent FROM customers c JOIN orders o ON o.customer_id = c.id GROUP BY c.city ORDER BY spent DESC NULLS LAST LIMIT 5

citytext	ordersbigint	pricedbigint	spentnumeric(10,2)
osaka	19	19	19330.56
lagos	11	10	11034.73
perth	13	11	10679.97
cairo	10	10	10415.33
quito	9	8	9719.07

5 rows · 114 examined · 2 pages read

Two counts, two answers

count(*) counts rows in the group. count(total) counts rows where total is not null. On a column with no nulls they agree, which is exactly why the difference goes unnoticed until the day it matters.

The empty group

Over no rows at all, count returns zero and every other aggregate returns null. That asymmetry is deliberate and it bites: a sum of nothing is not 0, and adding it to something else makes the whole expression null.

Zero, and then three nulls.

SELECT count(*) AS counted, sum(total) AS summed, avg(total) AS averaged FROM orders WHERE total > 999999

countedbigint	summednumeric(10,2)	averagednumeric
0	null	null

1 row · 90 examined · 1 page read

WHERE against HAVING

WHERE filters rows before grouping. HAVING filters groups after. That is the whole distinction, and it has a cost consequence: a condition that could have run in WHERE and was written in HAVING grouped rows it was always going to throw away.

WHERE picks the rows, HAVING picks the groups.

SELECT c.city, count(*) AS orders FROM customers c JOIN orders o ON o.customer_id = c.id WHERE o.shipped GROUP BY c.city HAVING count(*) > 3 ORDER BY orders DESC, c.city LIMIT 5

citytext	ordersbigint
osaka	15
lima	11
perth	8
lagos	7
cairo	6

5 rows · 114 examined · 2 pages read

Did it land?

How many, and how many priced
For each customer id that has orders, return the id, how many orders they placed, and how many of those have a total. Call the columns customer_id, placed and priced. Order by customer_id.
Only the busy cities
Return each city with more than five shipped orders, and that count, busiest first then by city. Call the count shipped_orders.

← Joins, as sets and as loops Subqueries, and when they are secretly a loop →