7. Aggregation: GROUP BY and HAVING¶
Most questions about data are questions about groups: how many orders each customer placed, the revenue of each month, the average salary in each department. This chapter turns many rows into one row per group: the five aggregate functions, the three ways to count, GROUP BY, the difference between WHERE and HAVING, and counting with conditions. It ends with the mistakes that cost the most marks in tests: dividing whole numbers, grouping by a name, and the columns PostgreSQL refuses but SQLite lets through.
In this chapter
You will learn:
COUNT,SUM,AVG,MIN,MAX, and what each does with NULLCOUNT(*),COUNT(column)andCOUNT(DISTINCT column), and when each is rightGROUP BYon one column, several columns and an expressionWHERE(before grouping) andHAVING(after grouping)- counting with a condition:
SUM(CASE ...)andFILTER - why
1 / 4is0, and how to get0.25 - the rule for what may appear in SELECT, and why SQLite breaks it
You need: chapters 2 and 3, and the sample database. Chapter 6 (NULL) helps.
7.1 Many rows in, one row out¶
An aggregate function reads many rows and returns one value. Without GROUP BY, the whole table is one group, so the answer is one row.
SELECT COUNT(*) AS products,
SUM(price) AS total_of_prices,
AVG(price) AS average_price,
MIN(price) AS cheapest,
MAX(price) AS dearest
FROM products;
Ran on PostgreSQL 16 and SQLite 3.53: same rows on both. 1 row.
| products | total_of_prices | average_price | cheapest | dearest |
|---|---|---|---|---|
| 11 | 338.75 | 30.7954545454545455 | 3.25 | 149.00 |
PostgreSQL prints the average with many decimal places; SQLite prints it as an ordinary decimal number. Round it
when you show it: ROUND(AVG(price), 2).
MIN and MAX work on text and dates too: the first and last in sorting order.
SELECT MIN(name) AS first_name, MAX(name) AS last_name,
MIN(signup_date) AS first_signup, MAX(signup_date) AS last_signup
FROM customers;
Ran on PostgreSQL 16 and SQLite 3.53: same rows on both. 1 row.
| first_name | last_name | first_signup | last_signup |
|---|---|---|---|
| Ana Silva | Ivan Petrov | 2025-11-03 | 2026-03-15 |
What every aggregate does with NULL: it skips it. SUM, AVG, MIN, MAX and COUNT(column) look only at
the values that are there. Only COUNT(*) counts rows whatever is in them.
7.2 Three ways to count¶
SELECT COUNT(*) AS customers,
COUNT(email) AS with_email,
COUNT(city) AS with_city,
COUNT(DISTINCT city) AS different_cities,
COUNT(DISTINCT name) AS different_names
FROM customers;
Ran on PostgreSQL 16 and SQLite 3.53: same rows on both. 1 row.
| customers | with_email | with_city | different_cities | different_names |
|---|---|---|---|---|
| 10 | 8 | 9 | 7 | 9 |
COUNT(*)counts rows: 10 customers.COUNT(email)counts rows whereemailis not NULL: two customers have no email, so 8.COUNT(DISTINCT city)counts different non-NULL values: one city is NULL and Toronto appears three times, so the nine known cities are only seven different ones.COUNT(DISTINCT name)is 9, not 10, because two different customers are both called Ana Silva. Counting names is not counting people.
Interview tip: which count?
Read the question for the noun. "How many customers" is COUNT(*) on customers (or COUNT(DISTINCT
customer_id) after a join). "How many customers have an email" is COUNT(email). "How many cities" is
COUNT(DISTINCT city), and the task should say whether an unknown city counts; by default it does not.
7.3 GROUP BY: one row per group¶
GROUP BY status puts the rows into one group per different status, and each aggregate is worked out once per group.
SELECT status, COUNT(*) AS orders
FROM orders
GROUP BY status
ORDER BY orders DESC, status;
Ran on PostgreSQL 16 and SQLite 3.53: same rows on both. 3 rows.
| status | orders |
|---|---|
| shipped | 16 |
| cancelled | 2 |
| pending | 2 |
The question "what is one row of the answer?" decides the GROUP BY. "Revenue of each order" means one row per
order, so group order_items by order_id:
SELECT order_id,
COUNT(*) AS lines,
SUM(quantity) AS units,
SUM(quantity * unit_price) AS revenue
FROM order_items
GROUP BY order_id
ORDER BY revenue DESC, order_id
LIMIT 5;
Ran on PostgreSQL 16 and SQLite 3.53: same rows on both. 5 rows.
| order_id | lines | units | revenue |
|---|---|---|---|
| 112 | 2 | 2 | 181.00 |
| 102 | 1 | 1 | 149.00 |
| 120 | 1 | 1 | 139.00 |
| 109 | 2 | 5 | 65.00 |
| 106 | 2 | 2 | 56.50 |
Several columns make one group per combination:
SELECT country, city, COUNT(*) AS customers
FROM customers
GROUP BY country, city
ORDER BY country, city;
Ran on PostgreSQL 16 and SQLite 3.53: same rows on both. 8 rows.
| country | city | customers |
|---|---|---|
| Canada | Toronto | 3 |
| China | Shanghai | 1 |
| Czechia | Prague | 1 |
| Israel | NULL | 1 |
| Japan | Osaka | 1 |
| Nigeria | Lagos | 1 |
| Portugal | Lisbon | 1 |
| South Korea | Seoul | 1 |
Eli Cohen's city is NULL, and NULLs form one group of their own: Israel's row has a NULL city. GROUP BY treats
all NULLs as equal to each other, even though NULL = NULL is not true anywhere else (chapter 6).
An expression can be grouped on too. Here the price band is worked out with CASE and then grouped; the SELECT repeats the same expression:
SELECT CASE WHEN price >= 30 THEN '30 and up' ELSE 'under 30' END AS band,
COUNT(*) AS products
FROM products
GROUP BY CASE WHEN price >= 30 THEN '30 and up' ELSE 'under 30' END
ORDER BY band;
Ran on PostgreSQL 16 and SQLite 3.53: same rows on both. 2 rows.
| band | products |
|---|---|
| 30 and up | 3 |
| under 30 | 8 |
PostgreSQL and SQLite also accept GROUP BY band (the alias) or GROUP BY 1 (the first SELECT column). Both are
handy in a timed test, but neither is standard SQL, and SQL Server accepts neither.
7.4 The SELECT rule¶
After GROUP BY, each output row stands for a whole group. So every column in SELECT must be either in the GROUP BY (the same for the whole group) or inside an aggregate (one value made from the group). Anything else has no single value. PostgreSQL refuses it. SQLite does not:
SELECT department, name, MAX(salary) AS top_salary
FROM employees
GROUP BY department;
PostgreSQL 16: error: column "employees.name" must appear in the GROUP BY clause or be used in an aggregate function
SQLite 3.53: no error, it runs and returns 4 rows.
SQLite picks a name from some row of each group. It has a documented special case: with exactly one MAX or
MIN, the bare column comes from the row that holds the maximum or minimum. With a tie, though, only one of the tied
rows is shown, and with any other aggregate the name is from an arbitrary row. On a test, write the query that
PostgreSQL would accept; chapter 13 shows the right way to get "the name of the
best-paid person in each department", ties included.
One exception
If you group by a table's primary key, PostgreSQL lets you select that table's other columns, because the key
decides them: GROUP BY c.customer_id allows c.name in SELECT. Writing GROUP BY c.customer_id, c.name works
everywhere and costs nothing.
7.5 WHERE or HAVING¶
Both filter, at different moments (chapter 3):
- WHERE removes rows before they are grouped. It can't see aggregates, because none exist yet.
- HAVING removes groups after they are made. It is the place for conditions on
COUNT,SUMand the rest.
"Customers with at least two shipped orders" needs both: WHERE keeps shipped orders, HAVING keeps customers with two or more of them.
SELECT customer_id, COUNT(*) AS shipped_orders
FROM orders
WHERE status = 'shipped'
GROUP BY customer_id
HAVING COUNT(*) >= 2
ORDER BY shipped_orders DESC, customer_id;
Ran on PostgreSQL 16 and SQLite 3.53: same rows on both. 3 rows.
| customer_id | shipped_orders |
|---|---|
| 1 | 5 |
| 2 | 2 |
| 4 | 2 |
An aggregate in WHERE is an error on both databases:
SELECT customer_id, COUNT(*) AS orders
FROM orders
WHERE COUNT(*) >= 2
GROUP BY customer_id;
PostgreSQL 16: error: aggregate functions are not allowed in WHERE
SQLite 3.53: error: misuse of aggregate: COUNT()
A condition on a plain column can go in either place and gives the same answer, but WHERE is the better home: it throws rows away before the work of grouping.
7.6 Counting with a condition¶
To count several things in one pass, put a CASE inside the aggregate. A row that doesn't match adds 0 (or gives NULL, which COUNT skips):
SELECT customer_id,
COUNT(*) AS orders,
SUM(CASE WHEN status = 'shipped' THEN 1 ELSE 0 END) AS shipped,
COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled,
COUNT(*) FILTER (WHERE status = 'pending') AS pending
FROM orders
WHERE customer_id IS NOT NULL
GROUP BY customer_id
ORDER BY customer_id;
Ran on PostgreSQL 16 and SQLite 3.53: same rows on both. 9 rows.
| customer_id | orders | shipped | cancelled | pending |
|---|---|---|---|---|
| 1 | 5 | 5 | 0 | 0 |
| 2 | 3 | 2 | 1 | 0 |
| 3 | 2 | 1 | 1 | 0 |
| 4 | 2 | 2 | 0 | 0 |
| 5 | 2 | 1 | 0 | 1 |
| 6 | 2 | 1 | 0 | 1 |
| 7 | 1 | 1 | 0 | 0 |
| 8 | 1 | 1 | 0 | 0 |
| 10 | 1 | 1 | 0 | 0 |
All three styles give the same kind of count. FILTER (WHERE ...) is standard SQL and works on PostgreSQL and on
SQLite 3.30 and later, but not on MySQL, SQL Server or Oracle; SUM(CASE ...) works everywhere, so it is the safe
habit. This is also how a pivot is written: one CASE per output column (more in
chapter 15).
7.7 Averages, ratios and whole-number division¶
"What share of orders were cancelled?" The obvious query is wrong:
The usual wrong answer
SELECT COUNT(CASE WHEN status = 'cancelled' THEN 1 END) / COUNT(*) AS cancelled_share
FROM orders;
Ran on PostgreSQL 16 and SQLite 3.53: same rows on both. 1 row.
| cancelled_share | | --- | | 0 |
Both counts are whole numbers, and dividing a whole number by a whole number gives a whole number in PostgreSQL, SQLite and SQL Server: 2 / 20 is 0, with the remainder thrown away. (MySQL and Oracle return 0.1 here.)
Make one side a decimal first, by multiplying by 1.0 (or 100.0 for a percentage), then round at the end:
SELECT ROUND(100.0 * COUNT(CASE WHEN status = 'cancelled' THEN 1 END) / COUNT(*), 1) AS cancelled_pct
FROM orders;
Ran on PostgreSQL 16 and SQLite 3.53: same rows on both. 1 row.
| cancelled_pct |
|---|
| 10.0 |
AVG itself is safe: it returns a decimal on both databases, even for whole numbers. The danger is only in /
between two whole numbers, including SUM(...) / COUNT(...) written out by hand.
AVG skips NULLs, which changes the answer when NULL means "zero":
SELECT ROUND(AVG(salary), 2) AS avg_known_salaries,
ROUND(SUM(salary) * 1.0 / COUNT(*), 2) AS avg_counting_missing_as_zero
FROM employees
WHERE department = 'Support';
Ran on PostgreSQL 16 and SQLite 3.53: same rows on both. 1 row.
| avg_known_salaries | avg_counting_missing_as_zero |
|---|---|
| 63500.00 | 42333.33 |
Leo Park's salary is not set. AVG averages the two known salaries; the second column divides by all three people.
Which is right depends on the question; usually it is the first, and saying why earns credit.
7.8 Grouping by the key, not the name¶
The usual wrong answer: orders per customer, grouped by name
SELECT c.name, COUNT(*) AS orders
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.name
ORDER BY orders DESC, c.name
LIMIT 3;
Ran on PostgreSQL 16 and SQLite 3.53: same rows on both. 3 rows.
| name | orders |
|---|---|
| Ana Silva | 6 |
| Ben Okafor | 3 |
| Chen Wei | 2 |
Ana Silva seems to have 6 orders. There are two Ana Silvas: customer 1 placed 5 and customer 10 placed 1.
Group by the key and show the name beside it:
SELECT c.customer_id, c.name, COUNT(*) AS orders
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name
ORDER BY orders DESC, c.customer_id
LIMIT 3;
Ran on PostgreSQL 16 and SQLite 3.53: same rows on both. 3 rows.
| customer_id | name | orders |
|---|---|---|
| 1 | Ana Silva | 5 |
| 2 | Ben Okafor | 3 |
| 3 | Chen Wei | 2 |
7.9 Summary¶
- Aggregates turn a group of rows into one value and skip NULLs; only
COUNT(*)counts every row. GROUP BYmakes one output row per different value (or combination); NULLs form one group.- Every SELECT column must be grouped or aggregated. PostgreSQL enforces it; SQLite lets it through and guesses.
WHEREfilters rows before grouping;HAVINGfilters groups after.- Count with a condition using
SUM(CASE WHEN ... THEN 1 ELSE 0 END). - Whole number divided by whole number is a whole number: multiply by
1.0first. - Group by the key, never by a name.
Practice¶
Try each one on the sample database before opening the answer.
7.1 How many orders are there, and how many different customers placed them? (The guest order has no customer.)
Answer
SELECT COUNT(*) AS orders, COUNT(DISTINCT customer_id) AS customers
FROM orders;
Ran on PostgreSQL 16 and SQLite 3.53: same rows on both. 1 row.
| orders | customers |
|---|---|
| 20 | 9 |
COUNT(DISTINCT customer_id) skips the NULL of the guest order, which is what "different customers" means.
7.2 For each category, the number of products and the average price rounded to 2 places, most products first.
Answer
SELECT category, COUNT(*) AS products, ROUND(AVG(price), 2) AS avg_price
FROM products
GROUP BY category
ORDER BY products DESC, category;
Ran on PostgreSQL 16 and SQLite 3.53: same rows on both. 4 rows.
| category | products | avg_price |
|---|---|---|
| Home | 4 | 17.69 |
| Office | 4 | 50.56 |
| Kitchen | 2 | 20.38 |
| NULL | 1 | 25.00 |
The gift card has no category, so it forms a group of its own with a NULL category. If the task wanted it left
out, add WHERE category IS NOT NULL. Where NULL sorts depends on the database: PostgreSQL puts NULLs last in
ascending order, SQLite first (chapter 6); here the sort is first by count, so it doesn't matter.
7.3 Total units sold and revenue for each product that has been sold, highest revenue first. Show the product id only.
Answer
SELECT product_id,
SUM(quantity) AS units,
SUM(quantity * unit_price) AS revenue
FROM order_items
GROUP BY product_id
ORDER BY revenue DESC, product_id;
Ran on PostgreSQL 16 and SQLite 3.53: same rows on both. 10 rows.
| product_id | units | revenue |
|---|---|---|
| 6 | 3 | 437.00 |
| 1 | 5 | 122.50 |
| 5 | 3 | 94.00 |
| 2 | 25 | 81.25 |
| 4 | 9 | 78.75 |
| 10 | 3 | 75.00 |
| 8 | 6 | 73.50 |
| 3 | 4 | 72.00 |
| 7 | 2 | 64.00 |
| 9 | 1 | 24.50 |
Revenue uses unit_price from order_items, the price actually paid. Note this counts items in every order,
cancelled ones too; the next question fixes that.
7.4 The same, but only for orders that were shipped. (You need a join; chapter 8 explains it.)
Answer
SELECT i.product_id,
SUM(i.quantity) AS units,
SUM(i.quantity * i.unit_price) AS revenue
FROM order_items i
JOIN orders o ON o.order_id = i.order_id
WHERE o.status = 'shipped'
GROUP BY i.product_id
ORDER BY revenue DESC, i.product_id;
Ran on PostgreSQL 16 and SQLite 3.53: same rows on both. 10 rows.
| product_id | units | revenue |
|---|---|---|
| 6 | 3 | 437.00 |
| 1 | 5 | 122.50 |
| 2 | 25 | 81.25 |
| 4 | 9 | 78.75 |
| 7 | 2 | 64.00 |
| 5 | 2 | 62.00 |
| 10 | 2 | 50.00 |
| 8 | 4 | 49.00 |
| 3 | 2 | 36.00 |
| 9 | 1 | 24.50 |
Each item has exactly one order, so joining items to orders doesn't multiply rows. The WHERE drops items of cancelled and pending orders before grouping.
7.5 Departments whose average known salary is above 75,000. Show the department and the average, rounded to a whole number.
Answer
SELECT department, ROUND(AVG(salary), 0) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 75000
ORDER BY avg_salary DESC;
Ran on PostgreSQL 16 and SQLite 3.53: same rows on both. 3 rows.
| department | avg_salary |
|---|---|
| Management | 180000 |
| Engineering | 118750 |
| Sales | 78750 |
The condition is on an aggregate, so it goes in HAVING. Use the unrounded AVG(salary) in HAVING so rounding
can't move a value across the line.
7.6 For each month of 2026 that has orders, the number of orders and the number of cancelled orders. Write the
month as text like 2026-01.
Answer
Taking the first seven characters of the date works on both databases, because SQLite stores these dates as
text YYYY-MM-DD and PostgreSQL turns a date into that same text when cast:
SELECT SUBSTR(CAST(order_date AS TEXT), 1, 7) AS month,
COUNT(*) AS orders,
SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM orders
GROUP BY SUBSTR(CAST(order_date AS TEXT), 1, 7)
ORDER BY month;
Ran on PostgreSQL 16 and SQLite 3.53: same rows on both. 4 rows.
| month | orders | cancelled |
|---|---|---|
| 2026-01 | 5 | 1 |
| 2026-02 | 6 | 0 |
| 2026-03 | 6 | 1 |
| 2026-04 | 3 | 0 |
The usual date functions differ between the two (to_char and date_trunc in PostgreSQL, strftime in
SQLite); chapter 16 has both.
7.7 The percentage of shipped orders that contain more than one product line, to one decimal place.
Answer
WITH lines AS (
SELECT i.order_id, COUNT(*) AS n
FROM order_items i
JOIN orders o ON o.order_id = i.order_id
WHERE o.status = 'shipped'
GROUP BY i.order_id
)
SELECT ROUND(100.0 * SUM(CASE WHEN n > 1 THEN 1 ELSE 0 END) / COUNT(*), 1) AS multi_line_pct
FROM lines;
Ran on PostgreSQL 16 and SQLite 3.53: same rows on both. 1 row.
| multi_line_pct | | --- | | 56.3 |
First one row per order with its line count (a CTE, chapter 11), then a
conditional count over those rows. 100.0 makes the division a decimal one.
7.8 Countries with more than one customer, and how many of those customers have no email.
Answer
SELECT country,
COUNT(*) AS customers,
COUNT(*) - COUNT(email) AS without_email
FROM customers
GROUP BY country
HAVING COUNT(*) > 1
ORDER BY country;
Ran on PostgreSQL 16 and SQLite 3.53: same rows on both. 1 row.
| country | customers | without_email |
|---|---|---|
| Canada | 3 | 0 |
Only Canada has more than one customer. COUNT(*) - COUNT(email) is the number of NULL emails, a neat trick
worth remembering.
7.9 Which query is wrong, and why? Both try to list customers with more than 100 of revenue in shipped orders.
-- A
SELECT o.customer_id, SUM(i.quantity * i.unit_price) AS revenue
FROM orders o JOIN order_items i ON i.order_id = o.order_id
WHERE o.status = 'shipped' AND SUM(i.quantity * i.unit_price) > 100
GROUP BY o.customer_id;
-- B
SELECT o.customer_id, SUM(i.quantity * i.unit_price) AS revenue
FROM orders o JOIN order_items i ON i.order_id = o.order_id
WHERE o.status = 'shipped'
GROUP BY o.customer_id
HAVING SUM(i.quantity * i.unit_price) > 100;
Answer
A is wrong: an aggregate can't be used in WHERE, and both databases stop with an error. B is right:
SELECT o.customer_id, SUM(i.quantity * i.unit_price) AS revenue
FROM orders o JOIN order_items i ON i.order_id = o.order_id
WHERE o.status = 'shipped'
GROUP BY o.customer_id
HAVING SUM(i.quantity * i.unit_price) > 100
ORDER BY revenue DESC, o.customer_id;
Ran on PostgreSQL 16 and SQLite 3.53: same rows on both. 3 rows.
| customer_id | revenue |
|---|---|
| 1 | 289.00 |
| 2 | 205.50 |
| 4 | 205.50 |
Customers 2 and 4 tie on 205.50, so the ORDER BY needs a second column to make the order certain. The guest order (customer NULL) would appear as a group of its own if its revenue passed the line; it is 50.00, so it doesn't.
7.10 The average number of orders per customer, counting customers who never ordered as 0. Round to 2 places.
Answer
Count the orders that have a customer and divide by the number of customers:
SELECT ROUND(1.0 * (SELECT COUNT(*) FROM orders WHERE customer_id IS NOT NULL)
/ (SELECT COUNT(*) FROM customers), 2) AS avg_orders_per_customer;
Ran on PostgreSQL 16 and SQLite 3.53: same rows on both. 1 row.
| avg_orders_per_customer | | --- | | 1.90 |
19 orders have a customer and there are 10 customers. Averaging COUNT(*) grouped by customer_id on orders
alone would miss Ivan Petrov, who has no orders and so no group: that gives 19 / 9. Chapter
8 shows the LEFT JOIN way, and its own trap.
Next: 8. Joins.