sql / intermediate
Snippet
Filtering Aggregated Results with the HAVING Clause
While the WHERE clause filters individual records before aggregation occurs, the HAVING clause filters summarized groups after the GROUP BY clause has evaluated. This allows logical conditions to be asserted directly against aggregate function results like COUNT() or AVG().
snippet.sql
sql
1
2
3
4
SELECT department_id, COUNT(employee_id) AS total_employees, AVG(salary) AS avg_salaryFROM employeesGROUP BY department_idHAVING COUNT(employee_id) >= 5 AND AVG(salary) > 50000;
Breakdown
1
SELECT department_id, COUNT(employee_id) AS total_employees, AVG(salary) AS avg_salary
Retrieves group identifiers and aggregate metric calculations.
2
FROM employees
Identifies the underlying dataset table.
3
GROUP BY department_id
Collapses rows sharing the same department_id into summary groups.
4
HAVING COUNT(employee_id) >= 5 AND AVG(salary) > 50000;
Applies conditional filters to aggregated summary groups after GROUP BY is processed.