What does GROUP BY do in SQL?

Instruction: Show how GROUP BY works with aggregates and explain WHERE versus HAVING.

Context: Practice grouping SQL rows into summaries and filtering those summaries with HAVING.

Updated

Example Answer

GROUP BY combines rows that share a key so I can calculate a summary for each group. For example, I can count employees in each department. WHERE filters rows before grouping; HAVING filters the grouped results. I normally select the grouping keys and aggregate expressions so the result is well-defined.

Small example

Assume an employees table with department_id and status:

SELECT department_id, COUNT(*) AS employee_count
FROM employees
WHERE status = 'active'
GROUP BY department_id
HAVING COUNT(*) >= 2
ORDER BY department_id;

This returns departments with at least two active employees. Rows with NULL in the grouping key form a group too. Some databases allow additional selected columns when they are functionally dependent on the grouping key; check the dialect before relying on that.

Reference

PostgreSQL: GROUP BY and HAVING

Related Questions