Core · DatabasesLesson 3 of 6Free to read

Grouping and counting · SQL and Databases

How do you summarise many rows into a few numbers?

Why you need it

Dashboards are summaries: sales per day, users per city, the average order. In SQL these come from aggregate functions and GROUP BY, and the database does the counting for you, even over millions of rows.

The intuition

GROUP BY city sorts the rows into buckets, one bucket per city. Then each aggregate function squeezes a bucket into one number:

COUNT(*): how many rows
SUM(x), AVG(x): total and average
MIN(x), MAX(x): smallest and largest

WHERE filters rows before grouping. HAVING filters buckets after grouping, so it can use COUNT(*).

Definition

Aggregate function

A function that turns many values into one: COUNT, SUM, AVG, MIN, MAX. Without GROUP BY the whole table is one group.

GROUP BY / HAVING

GROUP BY makes one result row per distinct value; HAVING keeps only the groups that pass a condition.

Examples

Whole-table numbers

Run it in the lesson

No GROUP BY: the whole table is one bucket.

1
2

SELECT COUNT(*) AS products, MIN(price) AS cheapest, MAX(price) AS dearest, AVG(price) AS average
FROM products;

Output

products | cheapest | dearest | average
5 | 1.5 | 25.0 | 10.8

Step by step

L1Five rows, one result row.5 | 1.5 | 25.0 | 10.8

Per category, then filter groups

Run it in the lesson

Group the products by category, then keep groups with more than one product.

SELECT category, COUNT(*) AS n, SUM(price) AS total
FROM products
GROUP BY category
HAVING COUNT(*) > 1
ORDER BY total DESC;

Output

category | n | total
home | 2 | 24.0
paper | 2 | 5.0

Step by step

L3Three buckets: bags (1), home (2), paper (2).
L4bags has only one product, so it is dropped.
L5home 24.0 comes before paper 5.0.home | 2 | 24.0

Common mistakes

A column that is not grouped

SELECT category, name, COUNT(*) … GROUP BY category asks for one name per category, but a category has several. Most databases reject it; SQLite quietly picks one, which is worse.

WHERE with COUNT

WHERE COUNT(*) > 1 is an error: WHERE runs before the groups exist. Use HAVING.

Quick quiz

1. What does this print?

SELECT customer_id, SUM(qty) AS items FROM orders GROUP BY customer_id HAVING SUM(qty) > 5 ORDER BY customer_id;

A
```
customer_id | items
1 | 14
3 | 7
```
B
```
customer_id | items
1 | 14
```
C
```
customer_id | items
1 | 14
2 | 3
3 | 7
```

Show the answer

Answer: A

Customer 1 bought 4 + 10 = 14, customer 2 bought 1 + 2 = 3, customer 3 bought 2 + 5 = 7, customer 4 bought 1. Only 14 and 7 are over 5.

2. You want only the days with at least 2 orders. Where does the condition go?

AWHERE COUNT(*) >= 2
BHAVING COUNT(*) >= 2
CORDER BY COUNT(*) >= 2

Show the answer

Answer: B

The condition is about a group (a day), so it belongs in HAVING, after GROUP BY day.

Practice

3 exercises in three steps: predict what a program prints, change a program to meet a new requirement, then write one from scratch. Your code runs in the browser and is checked the moment you submit.

PredictOrders per day
Type exactly what this query returns.
ModifyBusy customers only
The query counts orders per customer. Keep only customers with 2 or more orders.
WriteUnits per product
For each product_id in orders, return the product id and the total quantity sold as units. Sort by units, most first.

Practise this lesson

Open the interactive lesson to edit and run these examples and solve the exercises with instant feedback. Free to start, no sign-up.

Open the interactive lesson Course map

Interactive lessons open in order. If you have not done the earlier ones yet, you start at the course map.

Terms in this lesson

Term	Chinese	Definition
Aggregate	聚合	To combine many values into one, such as a count or a sum.
GROUP BY	分组	Splits rows into groups that share a value, one result row each.