GROUP BY and HAVING

Relax. We'll talk through this in plain words — no textbook voice.

What you'll walk away with

Write GROUP BY queries
Tell WHERE and HAVING apart
Use COUNT/SUM/AVG

Let's break it down simply

GROUP BY groups together rows that share the same column value. WHERE filters rows before grouping happens, while HAVING filters the aggregated results after grouping.

sql

SELECT department,
       COUNT(*) AS employee_count,
       ROUND(AVG(salary), 2) AS average_salary
FROM employees
WHERE active = TRUE
GROUP BY department
HAVING COUNT(*) >= 2
ORDER BY average_salary DESC;

You should see

department | employee_count | average_salary
Engineering | 4 | 1850000.00
Support | 2 | 950000.00

Try it yourself

From the orders table, calculate the total amount per customer and show only the customers whose total exceeds 100000.

Aggregate Functions Tutorial — PostgreSQL

Easy traps

Writing an aggregate condition inside WHERE
Leaving a non-aggregate column from SELECT out of GROUP BY

Exercise

From the orders table, calculate the total amount per customer and show only the customers whose total exceeds 100000.

You'll know it worked when: department | employee_count | average_salary Engineering | 4 | 1850000.00 Support | 2 | 950000.00