Grouping Data: GROUP BY and HAVING

GROUP BY is where aggregate functions become really powerful — instead of one summary for the whole table, you get one summary per group.

Basic grouping

SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;

This answers "how many employees are in each department?" — one row of output per distinct department_id.

The golden rule of GROUP BY

Every column in your SELECT list must either be (a) in the GROUP BY clause, or (b) wrapped in an aggregate function. This query is invalid because first_name is neither:

-- INVALID in most engines
SELECT department_id, first_name, COUNT(*)
FROM employees
GROUP BY department_id;

Grouping by multiple columns

SELECT department_id, job_title, COUNT(*) AS n
FROM employees
GROUP BY department_id, job_title;

Filtering groups with HAVING

WHERE filters individual rows before grouping. HAVING filters entire groups after aggregation — you cannot use an aggregate function in WHERE:

-- Departments with more than 4 employees
SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 4;

WHERE + GROUP BY + HAVING together

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
WHERE hire_date >= '2022-01-01'
GROUP BY department_id
HAVING AVG(salary) > 100000
ORDER BY avg_salary DESC;

Notice the logical order: WHERE runs first (filter rows), then GROUP BY (form groups), then HAVING (filter groups), then ORDER BY (sort the final result).

Open the Code Playground, pick any SQL engine (SQLite, PostgreSQL, MySQL, or SQL Server), and paste these queries in directly — the practice database above is already loaded for you, every time.

-- A ready-to-query practice database is loaded automatically before every run —
-- no CREATE TABLE needed. Available tables:
--   departments  (id, name, location)
--   employees    (id, first_name, last_name, email, department_id, job_title, salary, hire_date, manager_id)
--   customers    (id, customer_name, city, country, signup_date)
--   products     (id, product_name, category, price, stock_quantity)
--   orders       (id, customer_id, employee_id, order_date, status)
--   order_items  (id, order_id, product_id, quantity, unit_price)

SELECT * FROM employees WHERE department_id = 1;