How to use the HAVING clause

· Category: SQL & Databases

Short answer

HAVING filters groups after GROUP BY and aggregate functions have been applied, unlike WHERE which filters rows before aggregation.

Steps

  1. Filter groups by count: SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 5;
  2. Filter by average: SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 60000;
  3. Combine WHERE and HAVING: SELECT department, AVG(salary) FROM employees WHERE hire_date > '2024-01-01' GROUP BY department HAVING AVG(salary) > 50000;
  4. Use multiple conditions: HAVING COUNT(*) > 5 AND AVG(salary) > 50000
  5. Reference aggregate results by alias in some databases, or repeat the expression.

Tips

  • Use WHERE to reduce the row set before grouping for better performance.
  • HAVING can reference selected aggregates and grouping columns.

Common issues

  • Using HAVING without GROUP BY treats the entire result as one group.
  • Attempting to filter individual rows in HAVING is a logic error; use WHERE instead.r