← All lessons
Querying data · Lesson 3 of 7

GROUP BY and HAVING

Aggregate functions turn many rows into one value: COUNT, SUM, AVG, MIN and MAX.

SELECT COUNT(*) AS orders, MIN(ordered_on) AS first, MAX(ordered_on) AS last
FROM orders;
Returns
orders | first      | last
-------+------------+-----------
10     | 2024-09-01 | 2024-10-11
(1 row)

GROUP BY splits the rows into groups and gives one result row per group. Here, one row per category:

SELECT category, COUNT(*) AS products, ROUND(AVG(price), 2) AS avg_price
FROM products
GROUP BY category
ORDER BY category;
Returns
category | products | avg_price
---------+----------+----------
Books    | 2        | 13.88
Clothing | 2        | 49.5
Garden   | 3        | 27.33
Outdoors | 1        | 120
(4 rows)

The rule to remember: every column in the SELECT list is either in the GROUP BY or inside an aggregate. PostgreSQL and most other databases refuse a query that breaks it. SQLite and older MySQL settings let it through and quietly pick a value from one of the rows, which is a bug waiting to happen.

WHERE filters rows before they're grouped. HAVING filters the groups after, so it can use aggregates. Shipped orders only, then only customers with two or more of them:

SELECT customer_id, COUNT(*) AS orders
FROM orders
WHERE status = 'shipped'
GROUP BY customer_id
HAVING COUNT(*) >= 2;
Returns
customer_id | orders
------------+-------
1           | 3
2           | 2
(2 rows)

COUNT(*) counts rows. COUNT(city) counts the rows where city isn't NULL, and COUNT(DISTINCT city) counts the different values. The other aggregates skip NULLs too, so AVG is the average of the values that are there.

SELECT COUNT(*), COUNT(city), COUNT(DISTINCT city)
FROM customers;
Returns
COUNT(*) | COUNT(city) | COUNT(DISTINCT city)
---------+-------------+---------------------
8        | 7           | 3
(1 row)

Practice

How many customers live in each city? Show the city and the count.

The tables
  • customers (id, name, city, joined)
  • products (id, name, category, price)
  • orders (id, customer_id, ordered_on, status)
  • order_items (order_id, product_id, qty)
  • signups (id, email, name, signed_up_at)
  • payments (id, order_id, amount, currency, paid_at)
More info

Quick reference

COUNT(*)Rows in the group.
COUNT(col), COUNT(DISTINCT col)Non-NULL values; different values.
SUM, AVG, MIN, MAXAggregates; they skip NULLs.
GROUP BY a, bOne row per combination of a and b.
HAVING …Filter groups, after grouping. WHERE filters rows, before.
ROUND(x, 2)Round to two decimal places.

The other AI and data engineering courses