SQL GROUP BY and HAVING — Aggregating and Filtering Groups

Grouping and Filtering Aggregates

GROUP BY collapses rows that share a value into a single summary row. HAVING filters those groups after aggregation — it is to GROUP BY what WHERE is to SELECT.

Basic GROUP BY

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

GROUP BY with HAVING

SELECT department_id, COUNT(*) AS emp_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;

WHERE vs HAVING

Clause	Filters	Runs
WHERE	Individual rows	Before grouping
HAVING	Groups	After grouping

-- WHERE filters rows before grouping
-- HAVING filters groups after aggregation
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
WHERE is_active = true          -- filter rows first
GROUP BY department_id
HAVING AVG(salary) > 60000;    -- then filter groups

Multiple Aggregate Functions

SELECT
  d.name AS department,
  COUNT(*) AS headcount,
  AVG(e.salary) AS avg_salary,
  MIN(e.salary) AS min_salary,
  MAX(e.salary) AS max_salary
FROM employees e
JOIN departments d ON e.department_id = d.id
GROUP BY d.name
ORDER BY avg_salary DESC;

Grouping by Multiple Columns

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

Common Mistake

Every column in the SELECT that is not inside an aggregate function must appear in the GROUP BY clause. MySQL's ONLY_FULL_GROUP_BY mode (default since 5.7.5) enforces this.