Summarising Data: COUNT, SUM, GROUP BY & HAVING
Turn many rows into answers: aggregate functions (COUNT, SUM, AVG, MIN, MAX), the three kinds of COUNT, splitting rows into groups with GROUP BY, filtering groups with HAVING, and the rule that catches every beginner.
Aggregate functions: many rows in, one value out
An aggregate function looks at many rows and returns one value: how many, the total, the average, the smallest, the largest.
Aggregates skip NULL values, except COUNT(*), which counts rows.
Three kinds of COUNT
| Code | Counts | On customers |
|---|---|---|
COUNT(*) | rows | 20 |
COUNT(city) | rows where city is not NULL | 18 |
COUNT(DISTINCT country) | different values | the number of countries |
GROUP BY: one result row per group
GROUP BY splits the rows into groups that share a value, and runs the aggregates separately for each group. "The number of products per category" becomes:
Read the word "per" or "for each" in a question as GROUP BY. You can group by several columns (per country and city) or by a calculation (per month: GROUP BY strftime('%Y-%m', order_date)).
The golden rule
In a grouped query, every column in SELECT must either be in the GROUP BY, or be inside an aggregate. SELECT category, name, COUNT(*) … GROUP BY category is wrong: each category has many names, so which one should the database show? PostgreSQL refuses with an error; SQLite and MySQL quietly pick an arbitrary name, which is worse because you get a wrong answer without noticing.
HAVING: filtering groups
WHERE filters rows before grouping; HAVING filters groups after the aggregates are computed. "Countries with at least 3 customers":
You cannot write WHERE COUNT(*) >= 3: when WHERE runs, there are no groups yet. You can use both: WHERE to drop rows first (say, cancelled orders), HAVING to keep only the big groups.
Watch a query run clause by clause
Step through a grouped query in the order the database really runs it: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT.
Interactive: this part runs in your browser.
Try it: summarise the shop
Run the presets, then answer your own questions: how many orders per status? Which month had the most orders?
Interactive: this part runs in your browser.
Practice: check yourself
Write the query, then press ✔ Check my answer: your query runs on a fresh copy of the shop database and its result is compared with the correct one. ▶ Run just shows your result. Each run starts from the original data, so you cannot break anything.
Tables: customers(customer_id, name, city, country, joined) · products(product_id, name, category, price) · orders(order_id, customer_id, order_date, status) · order_items(order_id, product_id, quantity, unit_price)
Show each category and how many products it has, in a column products.
Show each order status and how many orders have it (orders), the most common status first.
Show each country with its number of customers (customers), but only countries with at least 3 customers.
From order_items, show each order_id and its total (quantity × unit_price, summed), for the 5 largest orders, largest first.
Summary
Key words
- aggregate function
- COUNT, SUM, AVG, MIN, MAX: many rows → one value
- GROUP BY
- splits rows into groups; aggregates run per group
- HAVING
- filters groups, after aggregation
- COUNT(*)
- counts rows, including NULLs
- COUNT(DISTINCT x)
- counts different values
Common mistakes
- Selecting a column that is neither grouped nor aggregated.
- Using an aggregate in WHERE instead of HAVING.
- Using COUNT(column) when you meant COUNT(*): NULLs are skipped.
- Averages of whole numbers that lose the decimals in your own formulas: multiply by 1.0.
- Forgetting ORDER BY, so groups come back in an arbitrary order.
Quiz
1. Customers has 20 rows and 2 NULL cities. What is COUNT(city)?
2. Which question needs GROUP BY?
3. Where do you filter on COUNT(*) > 5?
4. SELECT category, name, COUNT(*) FROM products GROUP BY category is a problem because…
5. In which order does the database run these?
📚 Further reading
Trusted, mostly free sources: official documentation, well-known textbooks and courses. When you want to confirm or go deeper on anything here, start with these.
- Select Star SQL ↗ a free interactive book on SQL
- Kaggle Learn: Intro to SQL and Advanced SQL ↗ free short courses with exercises on real data