GROUP BY & HAVING
Aggregate data per group and filter those groups
Free tier: read the explanation here. Upgrade to Pro for Drills, Speed challenges & Mastery badges.
UpgradeKnowledge Debt detected
You can study this freely — but your score may plateau if these foundations have gaps. The Mastery badge requires them to be solid.
Explanation
GROUP BY splits rows into groups and applies aggregates to each group.
-- Total sales per country
SELECT country, COUNT(*) AS orders, SUM(amount) AS revenue
FROM orders
GROUP BY country
ORDER BY revenue DESC;Rules:
- Every column in SELECT must either be in GROUP BY or inside an aggregate function
- Aggregates are computed per group, not for the whole table
HAVING — filters groups (like WHERE, but for aggregated data): ``sql -- Only countries with more than 100 orders SELECT country, COUNT(*) AS orders FROM orders GROUP BY country HAVING COUNT(*) > 100;
WHERE vs HAVING:
- WHERE filters rows BEFORE grouping
- HAVING filters groups AFTER aggregation
-- Filter rows first, then group, then filter groups
SELECT dept, AVG(salary) AS avg_sal
FROM employees
WHERE hire_year >= 2020 -- row filter (before GROUP BY)
GROUP BY dept
HAVING AVG(salary) > 70000; -- group filter (after GROUP BY)Examples
Top departments by average salary
Execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
SELECT
department,
COUNT(*) AS headcount,
ROUND(AVG(salary),0) AS avg_salary,
MAX(salary) AS top_salary
FROM employees
WHERE status = 'active'
GROUP BY department
HAVING COUNT(*) >= 5
ORDER BY avg_salary DESC
LIMIT 10;How well did you understand this?
Next in SQL & Databases
Subqueries & CASE WHEN