GROUP BY and HAVING: Summaries for Each Group

An aggregate on the whole table gives one number. Most real questions want one number for each group: orders per customer, sales per month, products per category. GROUP BY does this. It splits the rows into groups and runs the aggregate on each group separately. Learning GROUP BY is a big step. It is the tool behind most reports.

Your first GROUP BY

How many products are in each category?

SELECT category, COUNT(*) AS products
FROM products
GROUP BY category
ORDER BY category;

category   | products
Bags       | 1
Home       | 2
Office     | 2
Stationery | 3

The database puts all products with the same category together. It counts each pile and shows one row for each. The column after SELECT that is not inside an aggregate, here category, must also appear in GROUP BY. That is the main rule.

Several aggregates per group

SELECT
  category,
  COUNT(*)   AS products,
  MIN(price) AS cheapest,
  MAX(price) AS dearest
FROM products
GROUP BY category
ORDER BY category;

category   | products | cheapest | dearest
Bags       | 1        | 1400     | 1400
Home       | 2        | 300      | 650
Office     | 2        | 700      | 1800
Stationery | 3        | 60       | 120

You can add as many aggregates as you like. Each of them is worked out inside every group.

Group by more than one column

List several columns, separated by commas, to make groups from each combination. For example, the number of orders for every customer in each status:

SELECT customer_id, status, COUNT(*) AS orders
FROM orders
GROUP BY customer_id, status
ORDER BY customer_id, status;

Each different pair of customer and status becomes its own group. Customer 1 has two delivered orders and one shipped order, so customer 1 gives two rows.

Groups made from NULL

All NULL values are put in one group together. Look at orders per city for customers.

SELECT city, COUNT(*) AS customers
FROM customers
GROUP BY city
ORDER BY customers DESC, city;

city      | customers
Nairobi   | 2
NULL      | 1
Bengaluru | 1
Chennai   | 1
Lagos     | 1
London    | 1
Toronto   | 1

Nairobi has two customers. The row with NULL is Hari, who has no city. If you do not want that row, add WHERE city IS NOT NULL before the GROUP BY.

HAVING: filter the groups

Suppose you only want categories with at least two products. You might try WHERE COUNT(*) >= 2, but that fails. WHERE works on single rows before any groups exist. To filter groups after counting, use HAVING.

SELECT category, COUNT(*) AS products
FROM products
GROUP BY category
HAVING COUNT(*) >= 2;

category   | products
Home       | 2
Office     | 2
Stationery | 3

Bags has only one product, so its group is removed.

WHERE versus HAVING

This is one of the most common SQL interview questions, so learn it well.

• WHERE filters rows before grouping. It cannot use aggregates.
• HAVING filters groups after grouping. It is where you use aggregates like COUNT(*) or SUM(x).
• You can use both in one query. Use WHERE first to throw away rows you do not need, because that is faster, then HAVING for the group test.

Here both are used together. Count each customer's orders that were not cancelled, and keep only customers with two or more.

SELECT customer_id, COUNT(*) AS orders
FROM orders
WHERE status <> 'cancelled'
GROUP BY customer_id
HAVING COUNT(*) >= 2
ORDER BY customer_id;

customer_id | orders
1           | 3
2           | 2

The full order of a query

Now that you know most parts, here is the full written order, which is also the order you must write them in. The database works in a different logical order, shown on the right.

Write it like this     The database thinks like this
SELECT                 5. choose columns
FROM                   1. find the table
WHERE                  2. keep rows
GROUP BY               3. make groups
HAVING                 4. keep groups
ORDER BY               6. sort
LIMIT                  7. cut

If you ever wonder why something does not work, check this order. For example, WHERE runs before SELECT, so a new alias from SELECT is not known inside WHERE. In many engines ORDER BY runs after SELECT, so it can use the alias.

Common mistakes

• A selected column that is not in GROUP BY and not inside an aggregate. Some engines such as SQLite and older MySQL allow it and pick an arbitrary row's value, which gives wrong answers quietly. PostgreSQL and SQL Server give an error. Treat it as an error.
• Using WHERE where HAVING is needed.
• Counting groups when you meant to count rows, or the reverse. Always ask: what is one row of the answer? Here it is one category, or one customer.

Your task

1. Count the customers in each city, leaving out customers with no city.
2. Show the number of orders in each status, most common first.
3. Show each order's id and the total quantity of items in it from order_items, and keep only orders with more than 6 items. Orders 5 and 7 should appear.
4. Count orders per month with SUBSTR(order_date, 1, 7) as the month.

How to check it worked

• Nairobi has 2 customers and the others 1.
• Delivered is the most common status with 6 orders.
• The monthly counts are 3 (2024-06), 5 (2024-07) and 2 (2024-08).