HAVING
HAVING filters groups after aggregation, whereas WHERE filters rows before it.
In this page:
Syntax
SELECT group_column, aggregate_function(column)
FROM table_name
GROUP BY group_column
HAVING aggregate_function(column) > value;HAVING
Use WHERE to discard rows before grouping and HAVING to keep only groups that satisfy an aggregate condition such as SUM(qty) > 10. HAVING can reference aggregates, but WHERE cannot. Filtering with WHERE first is more efficient.
Note:
Use WHERE for row filters and HAVING only for aggregate conditions.
Example: HAVING
CREATE TABLE sales (id INTEGER PRIMARY KEY, item TEXT, qty INTEGER);
INSERT INTO sales VALUES (1,'pen',5),(2,'book',1),(3,'pen',10),(4,'book',3),(5,'lamp',2);
SELECT item, SUM(qty) AS total FROM sales GROUP BY item HAVING SUM(qty) >= 4 ORDER BY item;
SELECT item, COUNT(*) AS n FROM sales WHERE qty > 1 GROUP BY item HAVING COUNT(*) >= 2;
-- Output:
-- item | total
-- book | 4
-- pen | 15
-- item | n
-- pen | 2Login to try C/C++/Java/PHP code in the editor
Related Topics
Common Mistakes
- Using an aggregate in WHERE
- Using HAVING for row-level filters
- Forgetting HAVING runs after GROUP BY
Chapter Summary
- WHERE filters rows
- HAVING filters groups
- Aggregates belong in HAVING
- Filter early for speed
🔒
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: