GROUP BY & HAVING

You saw GROUP BY in the aggregation basics. This goes deeper, especially HAVING, and the difference between WHERE and HAVING which comes up in like every interview.

quick recap of GROUP BY

SELECT customer_id, COUNT(*) AS orders, SUM(amount) AS spent
FROM orders
GROUP BY customer_id;

One output row per customer_id. Every column you select is either in GROUP BY or inside an aggregate.

HAVING = WHERE but for groups

"Customers who placed 2 or more orders". You can't write WHERE COUNT(*) >= 2 because WHERE runs on single rows before any grouping happens. The count doesn't exist yet.

SELECT customer_id, COUNT(*) AS orders
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 2;

WHERE vs HAVING, side by side

SELECT customer_id, SUM(amount) AS spent
FROM orders
WHERE status = 'delivered'        -- 1. throw out non-delivered rows
GROUP BY customer_id              -- 2. make piles per customer
HAVING SUM(amount) > 2000;        -- 3. throw out piles with small totals

• WHERE filters rows, before grouping
• HAVING filters groups, after grouping

Rule of thumb, if the condition has an aggregate in it (COUNT, SUM...), HAVING. Otherwise WHERE. You can put normal conditions in HAVING but it's slower because it groups everything first and filters after.

the full order again

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT

That's why in postgres you can't use a SELECT alias in HAVING either (mysql lets you, postgres doesn't), just repeat the expression.

grouping by expressions

You don't have to group by a plain column.

-- orders per month
SELECT DATE_TRUNC('month', order_date) AS month, COUNT(*), SUM(amount)
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

-- customers by first letter
SELECT LEFT(first_name, 1) AS letter, COUNT(*)
FROM customers
GROUP BY LEFT(first_name, 1);

grouping after a join

Most real group bys are after a join.

SELECT p.category, SUM(oi.quantity) AS units, SUM(oi.quantity * p.price) AS revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
GROUP BY p.category
HAVING SUM(oi.quantity) >= 4
ORDER BY revenue DESC;

finding duplicates

GROUP BY + HAVING is the standard way to find duplicate values:

SELECT city, COUNT(*)
FROM customers
GROUP BY city
HAVING COUNT(*) > 1;

Swap city for email in a real users table and you've found duplicate signups.

stuff that trips people up

• aggregates in WHERE, always an error.
• selecting columns not in GROUP BY.
• using an alias in HAVING (works in mysql, fails in postgres).
• GROUP BY name when two people can share a name, group by the id.

practice

Check queries.sql.

-- GROUP BY & HAVING
-- run sample-data.sql first

-- examples ---------------------------------------------------------

SELECT customer_id, COUNT(*) AS orders
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 2;

SELECT customer_id, SUM(amount) AS spent
FROM orders
WHERE status = 'delivered'
GROUP BY customer_id
HAVING SUM(amount) > 2000;

SELECT DATE_TRUNC('month', order_date) AS month, COUNT(*), SUM(amount)
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

SELECT city, COUNT(*) FROM customers GROUP BY city HAVING COUNT(*) > 1;


-- practice ---------------------------------------------------------

-- 1. departments with more than 2 employees
-- 2. departments where the average salary is above 100000
-- 3. categories where the cheapest product costs more than 200
-- 4. customers (show their name) who spent more than 1000 total on non-cancelled orders
-- 5. months in 2024 that had more than 1 order
-- 6. customers who have ordered from more than one category


-- answers ----------------------------------------------------------

-- 1
SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 2;

-- 2
SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 100000;

-- 3
SELECT category, MIN(price) FROM products GROUP BY category HAVING MIN(price) > 200;

-- 4
SELECT c.first_name, SUM(o.amount) AS spent
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status <> 'cancelled'
GROUP BY c.customer_id, c.first_name
HAVING SUM(o.amount) > 1000;

-- 5
SELECT DATE_TRUNC('month', order_date) AS month, COUNT(*)
FROM orders
WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01'
GROUP BY 1
HAVING COUNT(*) > 1
ORDER BY 1;

-- 6
SELECT o.customer_id, COUNT(DISTINCT p.category) AS categories
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
GROUP BY o.customer_id
HAVING COUNT(DISTINCT p.category) > 1;