HAVING
SQL · Grouping Rows
Theory
HAVING filters groups after GROUP BY has built them and the aggregates are computed. WHERE asks "should this row be counted?" while HAVING asks "should this group be shown?". GROUP BY dept HAVING COUNT(*) > 1 keeps only departments with more than one employee.
Because HAVING runs after grouping, it can use aggregate functions like COUNT, SUM and AVG, which WHERE cannot. A query can use both: WHERE city = 'Jaipur' first removes rows, then GROUP BY forms groups from what remains, then HAVING SUM(amount) > 10000 removes small groups. Conditions that do not involve aggregates belong in WHERE, because filtering early means less data to group. Most engines do not let HAVING use SELECT aliases, so repeat the aggregate expression.
Why we use it
Many questions are about groups that meet a threshold: customers with more than five orders, cities with sales above a target, departments with an average salary over ₹60,000. HAVING filters on those aggregate results.
When to use it
Use HAVING only for conditions on aggregates. Put plain row conditions in WHERE so they run before grouping. Without GROUP BY, HAVING treats the whole table as one group, which is rarely what you want.
How it works
- FROM and WHERE produce the filtered rows
- GROUP BY forms the groups
- Compute aggregates for each group
- Does the group satisfy the HAVING condition? Yes: Keep the group. No: Discard the group.
- SELECT and ORDER BY the kept groups
Syntax
SELECT column, AGG(column)
FROM table
GROUP BY column
HAVING AGG(column) condition;Example
SELECT department, COUNT(*) FROM employees
GROUP BY department HAVING COUNT(*) > 1;Key points
- Filters groups; WHERE filters rows
- Executes after GROUP BY in logical query order
- Can use aggregates like COUNT, SUM, AVG
- Put non-aggregate conditions in WHERE for speed
- Most engines need the aggregate repeated instead of a SELECT alias