Introduction
GROUP BY splits the rows returned by a query into buckets that share the same value in one or more columns, so that aggregate functions like COUNT, SUM, and AVG operate per bucket instead of over the whole table. HAVING then filters those aggregated groups, playing a role similar to WHERE but applied after grouping instead of before. Confusing WHERE and HAVING is one of the most common SQL mistakes beginners make.
Cricket analogy: GROUP BY sorts every ball bowled in a season into buckets per bowler so you can compute each one's total wickets, then HAVING keeps only bowlers with wickets > 20 for the award shortlist, a distinction rookies often mix up.
Syntax
SELECT column1, AGG_FUNC(column2) AS alias
FROM table_name
WHERE row_level_condition
GROUP BY column1
HAVING group_level_condition
ORDER BY column1;Explanation
SQL's logical processing order is roughly FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY. WHERE filters individual rows before any grouping happens, so it cannot reference aggregate functions like COUNT() or SUM(). GROUP BY then collapses the remaining rows into groups based on the specified columns. HAVING filters those already-formed groups using conditions on aggregate values, which is why HAVING can use COUNT(), SUM(), AVG() etc. while WHERE cannot. Every column in the SELECT list that is not inside an aggregate function must appear in the GROUP BY clause.
Cricket analogy: First WHERE removes rain-abandoned matches from the raw list before anything is counted, then GROUP BY collapses remaining innings into per-batter buckets, and only then can HAVING check SUM(runs) since WHERE at that early stage cannot reference an aggregate like total runs yet.
Example
-- orders table
-- order_id | customer_id | status | amount
-- 1 | 101 | completed | 250
-- 2 | 101 | completed | 100
-- 3 | 102 | cancelled | 400
-- 4 | 103 | completed | 50
-- 5 | 103 | completed | 75
SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS total_spent
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
HAVING SUM(amount) > 100
ORDER BY total_spent DESC;Output
WHERE status = 'completed' first removes the cancelled order (order_id 3), leaving orders 1, 2, 4, and 5. GROUP BY customer_id then forms two groups: customer 101 (250 + 100 = 350) and customer 103 (50 + 75 = 125). HAVING SUM(amount) > 100 keeps both groups since 350 > 100 and 125 > 100. The result is customer_id 101 with order_count 2 and total_spent 350, followed by customer_id 103 with order_count 2 and total_spent 125, ordered by total_spent descending.
Cricket analogy: WHERE status = 'completed' first drops the rained-off match, leaving four valid innings; GROUP BY batter forms two groups, Kohli (55+45=100) and Rahul (30+20=50); HAVING SUM(runs) > 40 keeps both, ordered by total runs descending.
Key Takeaways
- WHERE filters individual rows before grouping; HAVING filters groups after aggregation.
- HAVING can reference aggregate functions like COUNT() or SUM(); WHERE cannot.
- Logical processing order is FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY.
- Non-aggregated columns in SELECT must appear in the GROUP BY clause.
- You can use both WHERE and HAVING in the same query to filter at different stages.
Practice what you learned
1. Which clause would you use to filter out groups where SUM(amount) is less than 1000?
2. Why can't you write WHERE COUNT(*) > 5 in a standard SQL query?
3. In the logical query processing order, which comes first?
4. A query has SELECT department, AVG(salary) FROM employees GROUP BY department. What rule must be followed?
Was this page helpful?
You May Also Like
Aggregate Functions in SQL
Learn how COUNT, SUM, AVG, MIN, and MAX summarize sets of rows into single values, and how each handles NULLs.
Filtering with WHERE
Use the WHERE clause to filter rows returned by a query based on specified conditions.
Window Functions
Functions that compute values across a set of related rows without collapsing them into a single output row, using OVER().