sql / intermediate
Snippet
Conditional Aggregation Filtering with the HAVING Clause
While the WHERE clause filters individual rows before aggregation occurs, the HAVING clause filters summarized groups after the GROUP BY operation has computed aggregate expressions like COUNT or AVG.
snippet.sql
sql
1
2
3
4
5
SELECT department_id, COUNT(employee_id) AS staff_count, AVG(salary) AS avg_salaryFROM employeesWHERE status = 'ACTIVE'GROUP BY department_idHAVING COUNT(employee_id) >= 5 AND AVG(salary) > 50000;
Breakdown
1
SELECT department_id, COUNT(employee_id) AS staff_count, AVG(salary) AS avg_salary
Selects group identifier and defines aggregate calculation aliases.
2
FROM employees
Designates the base employees table for input data.
3
WHERE status = 'ACTIVE'
Filters individual row records prior to grouping operations.
4
GROUP BY department_id
Collapses pre-filtered rows into aggregated buckets per department.
5
HAVING COUNT(employee_id) >= 5 AND AVG(salary) > 50000;
Evaluates aggregate conditions to discard groups that do not meet headcount and salary thresholds.