SQL Aggregation
HAVING Clause
Filter groups after aggregation with the HAVING clause.
- WHERE filters rows β before grouping occurs
- HAVING filters groups β after aggregation is complete Think of HAVING as WHERE's post-grouping partner.
What is HAVING?
DfHAVING Clause
A HAVING clause filters the results of a GROUP BY query, applying conditions to grouped rows after the aggregation function has been evaluated.
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;
DfExecution Order
SQL processes clauses in this order: FROM β WHERE β GROUP BY β HAVING β SELECT β ORDER BY. HAVING always runs after GROUP BY.
-- Filter departments with average salary above 60000
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 60000;
HAVING can reference alias names defined in the SELECT clause, making queries cleaner and easier to read.
-- Using alias in HAVING
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING avg_salary > 60000;
WHERE vs HAVING
DfKey Difference
WHERE filters individual rows before grouping. HAVING filters groups after aggregation. They serve different purposes in the query lifecycle.
-- WHERE filters rows before grouping
SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE hire_date > '2020-01-01'
GROUP BY department
HAVING AVG(salary) > 60000;
| Feature | WHERE | HAVING |
|---|---|---|
| Applies to | Individual rows | Grouped rows |
| Timing | Before GROUP BY | After GROUP BY |
| Aggregates | Cannot use | Can use |
| Performance | Faster | Slower |
Multiple Conditions in HAVING
-- Multiple HAVING conditions
SELECT department, job_title, COUNT(*) AS cnt
FROM employees
GROUP BY department, job_title
HAVING COUNT(*) > 3 AND AVG(salary) > 50000;
-- Combining OR and AND in HAVING
SELECT department, COUNT(*) AS cnt, SUM(salary) AS total
FROM employees
GROUP BY department
HAVING COUNT(*) > 5 OR SUM(salary) > 500000;
HAVING with Aggregate Functions
-- Using multiple aggregate functions in HAVING
SELECT department,
MIN(salary) AS min_salary,
MAX(salary) AS max_salary,
AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING MAX(salary) - MIN(salary) > 30000;
-- HAVING with COUNT and SUM
SELECT department, COUNT(*) AS cnt, SUM(salary) AS total
FROM employees
GROUP BY department
HAVING COUNT(*) >= 10 AND SUM(salary) > 1000000;
Do not confuse WHERE and HAVING. Using aggregate functions in WHERE will cause an error. Use HAVING for aggregate conditions.
Real-World Examples
-- Find departments with high turnover (many terminated employees)
SELECT department,
COUNT(*) AS total_employees,
SUM(CASE WHEN status = 'terminated' THEN 1 ELSE 0 END) AS terminated
FROM employees
GROUP BY department
HAVING SUM(CASE WHEN status = 'terminated' THEN 1 ELSE 0 END) > 3;
-- Identify products with insufficient stock
SELECT category,
AVG(stock_quantity) AS avg_stock,
MIN(stock_quantity) AS min_stock
FROM products
GROUP BY category
HAVING MIN(stock_quantity) < 10;
HAVING with JOIN
-- HAVING with joined tables
SELECT d.department_name, COUNT(*) AS employee_count
FROM employees e
JOIN departments d ON e.department_id = d.id
GROUP BY d.department_name
HAVING COUNT(*) > 10;
-- Complex HAVING with multiple joins
SELECT d.department_name,
e.job_title,
COUNT(*) AS cnt
FROM employees e
JOIN departments d ON e.department_id = d.id
JOIN salaries s ON e.id = s.employee_id
GROUP BY d.department_name, e.job_title
HAVING AVG(s.salary) > 80000 AND COUNT(*) > 5;
Common HAVING Patterns
Key Takeaways
- HAVING filters grouped results after aggregation is applied.
- WHERE filters individual rows before grouping occurs.
- HAVING can reference SELECT aliases for cleaner queries.
- Multiple aggregate conditions can be combined with AND/OR.
- HAVING supports all aggregate functions: COUNT, SUM, AVG, MIN, MAX.
| Pattern | Use Case |
|---|---|
| HAVING COUNT(*) > N | Find groups with minimum size |
| HAVING AVG(col) > N | Filter by average value |
| HAVING SUM(col) > N | Filter by total value |
| HAVING MIN/MAX | Filter by extreme values |
| Multiple conditions | Complex group filtering |