SQL & Databases
Topic 6 · Querying data · ⏱ about 2 h

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.

Show:
In this topic
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.

SELECT COUNT(*) AS products, MIN(price) AS cheapest, MAX(price) AS dearest, ROUND(AVG(price), 2) AS average_price, SUM(price) AS total_if_you_bought_one_of_each FROM products;

Aggregates skip NULL values, except COUNT(*), which counts rows.

Beginner

Three kinds of COUNT

CodeCountsOn customers
COUNT(*)rows20
COUNT(city)rows where city is not NULL18
COUNT(DISTINCT country)different valuesthe number of countries
Beginner

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:

SELECT category, COUNT(*) AS products, ROUND(AVG(price), 2) AS avg_price FROM products GROUP BY category ORDER BY products DESC;

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)).

Beginner

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.

Beginner

HAVING: filtering groups

WHERE filters rows before grouping; HAVING filters groups after the aggregates are computed. "Countries with at least 3 customers":

SELECT country, COUNT(*) AS customers FROM customers GROUP BY country HAVING COUNT(*) >= 3;

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.

Try it

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

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.

Beginner

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)

Exercise 1. Products per category

Show each category and how many products it has, in a column products.

Exercise 2. Orders by status

Show each order status and how many orders have it (orders), the most common status first.

Exercise 3. Countries with 3+ customers

Show each country with its number of customers (customers), but only countries with at least 3 customers.

Exercise 4. Revenue per order

From order_items, show each order_id and its total (quantity × unit_price, summed), for the 5 largest orders, largest first.

Beginner

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.
Check yourself

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?

Go deeper

📚 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.

Ready