Reference · module 4
Totals and groups
Everything this module explains, on one page.
COUNT, SUM, AVG
Turn many rows into one number.
- Aggregate functions turn many rows into one value. SELECT COUNT(*) FROM orders; returns how many rows there are, while COUNT(phone) counts only rows where phone is not NULL.
- SUM(total) adds a column up and AVG(total) gives its average. Both skip NULL values, so the average of 10, NULL and 20 is 15, not 10.
- MIN and MAX find the smallest and largest value, and they work on text and dates too. WHERE runs before the aggregate: SELECT MAX(total) FROM orders WHERE paid = 1;.
The mistake you are about to make
SELECT COUNT(phone) FROM customers;
SELECT COUNT(*) FROM customers;
It seems not to matter what goes inside COUNT — rows are rows. COUNT(column) skips NULL: customers without a phone drop out, and you get fewer customers than there are.
GROUP BY
Get one total per group instead of one for the whole table.
- GROUP BY splits rows into groups and runs the aggregate on each: SELECT city, COUNT(*) FROM customers GROUP BY city; gives one row per city with its number of customers.
- Each group becomes exactly one row. So next to aggregates you select only the grouping columns: a whole city has no single name.
- Group by several columns to get finer groups: GROUP BY city, year gives one row for each city and year that appear together in the table.
The mistake you are about to make
SELECT city, name, COUNT(*) FROM customers GROUP BY city;
SELECT city, COUNT(*) FROM customers GROUP BY city;
You want the name too while you are at it. Strict databases reject this query; SQLite runs it and picks some customer's name from the group — an answer that looks true.
HAVING
Filter groups, and know which filter runs when.
- HAVING filters groups, just as WHERE filters rows: GROUP BY city HAVING COUNT(*) >= 2 keeps only the cities with at least two customers.
- The order of work is fixed: WHERE drops rows first, then GROUP BY forms groups, then HAVING drops groups. That is why WHERE cannot use COUNT — the groups do not exist yet.
- Groups sort like rows: ORDER BY COUNT(*) DESC puts the biggest group first. A full query reads SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT.
The mistake you are about to make
SELECT city FROM customers WHERE COUNT(*) > 1 GROUP BY city;
SELECT city FROM customers GROUP BY city HAVING COUNT(*) > 1;
A condition is a condition, and WHERE is where conditions usually go. WHERE runs before grouping, when there is nothing to count yet, and the database replies misuse of aggregate.