SQL GROUP BY & HAVING Cheat Sheet

GROUP BY: create one result row per group

GROUP BY combines rows that share the same selected category. Every selected column must either be grouped or calculated with an aggregate function.

• COUNT(*) counts rows
• COUNT(column) ignores NULL values
• Give calculated columns clear aliases

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

WHERE before grouping, HAVING after grouping

WHERE filters individual rows before aggregation. HAVING filters the grouped result after aggregates have been calculated.

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

Conditional aggregation

CASE inside an aggregate lets you calculate several business metrics in one grouped query.

SELECT
  department_id,
  COUNT(*) AS total,
  SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active_count
FROM employees
GROUP BY department_id;

Common mistakes

Check the row count before and after joins. A one-to-many join can multiply rows and inflate every aggregate that follows.

• Using WHERE with an aggregate expression
• Selecting an ungrouped column
• Counting a nullable column unintentionally
• Hiding a duplicated join with DISTINCT