Lesson 8 • Intermediate
GROUP BY & HAVING
Group rows by category and filter groups to build powerful analytical reports — revenue by department, orders per customer, and more.
🎯 What You'll Learn
- Group rows by one or more columns
- Apply aggregates to each group (COUNT, SUM, AVG per category)
- Filter groups with HAVING
- The critical difference between WHERE and HAVING
GROUP BY Basics
GROUP BY splits your data into groups, then applies aggregate functions to each group separately. Without GROUP BY, aggregates summarize the entire table.
🗂️ Real-World Analogy
Imagine sorting a pile of receipts into folders by store name. Then for each folder, you calculate: total spent, number of visits, average purchase. That's exactly what GROUP BY store + aggregates does!
⚠️ The #1 GROUP BY Rule
Every column in your SELECT must either be in GROUP BY or wrapped in an aggregate function. You can't SELECT name if you're grouping by department — which "name" would SQL show for the group?
Basic GROUP BY
Group rows and calculate aggregates per category
-- Count orders per customer
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;
-- Total sales by category
SELECT category, SUM(price * quantity) AS total_sales
FROM products
GROUP BY category
ORDER BY total_sales DESC;
-- Average salary by department
SELECT department,
ROUND(AVG(salary), 2) AS avg_salary,
COUNT(*) AS num_employees
FROM employees
GROUP BY department;Multi-Column Grouping
Create subgroups with multiple GROUP BY columns
-- Group by multiple columns
-- Sales by category AND year
SELECT
category,
EXTRACT(YEAR FROM sale_date) AS sale_year,
SUM(amount) AS total_sales,
COUNT(*) AS num_transactions
FROM sales
GROUP BY category, EXTRACT(YEAR FROM sale_date)
ORDER BY category, sale_year;
-- Revenue by department and job title
SELECT
department,
job_title,
COUNT(*) AS headcount,
ROUND(AVG(salary), 0) AS avg_salary
FROM employees
GROUP BY department, job_title
ORDER BY department, avg_s
...HAVING — Filter Groups
HAVING filters groups after aggregation. It's like WHERE, but for aggregated results.
| Clause | Filters | When |
|---|---|---|
| WHERE | Individual rows | Before grouping |
| HAVING | Groups | After grouping & aggregation |
💡 Pro Tip
Use WHERE whenever possible — it reduces data before grouping, making queries faster. Only use HAVING when you need to filter on an aggregated value (like HAVING COUNT(*) > 5).
HAVING Clause
Filter groups that meet aggregate conditions
-- HAVING: filter GROUPS (not rows)
-- Customers with more than 5 orders
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;
-- Departments with avg salary above 70k
SELECT department, ROUND(AVG(salary), 0) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 70000
ORDER BY avg_salary DESC;
-- Categories with total sales over 10000
SELECT category, SUM(amount) AS total_sales
FROM sales
GROUP BY category
HAVING SUM(amount) > 10000;The Complete SQL Query Order
Here's how all the clauses fit together:
SELECT columns / aggregates FROM table WHERE row_conditions -- 1. Filter rows GROUP BY columns -- 2. Group remaining rows HAVING group_conditions -- 3. Filter groups ORDER BY columns -- 4. Sort results LIMIT n; -- 5. Limit output
This order is strict — you'll get a syntax error if you rearrange them.
WHERE + GROUP BY + HAVING Together
Build complete analytical queries combining all clauses
-- WHERE vs HAVING: they work together!
-- WHERE filters ROWS (before grouping)
-- HAVING filters GROUPS (after grouping)
SELECT
department,
COUNT(*) AS active_employees,
ROUND(AVG(salary), 0) AS avg_salary
FROM employees
WHERE status = 'active' -- filter: only active employees
GROUP BY department
HAVING COUNT(*) >= 5 -- filter: departments with 5+ people
ORDER BY avg_salary DESC;
-- Real-world: top product categories this year
SELECT
category,
COUNT
...📘 Quick Reference
| Pattern | Purpose |
|---|---|
| GROUP BY col | Group rows by column value |
| GROUP BY a, b | Create subgroups |
| HAVING COUNT(*) > n | Keep groups with n+ rows |
| HAVING SUM(x) > y | Keep groups where sum exceeds y |
| WHERE + GROUP BY + HAVING | Filter rows, group, filter groups |
🎉 Lesson Complete!
You can now group data and filter groups — essential skills for data analysis and reporting. Next, you'll learn subqueries — nesting one query inside another for even more powerful results!
Sign up for free to track which lessons you've completed and get learning reminders.