🍬 Introduction: The Skittles Pile
Imagine you buy a giant bag of Skittles candy. You dump them all on the table:
- The Pile Sorter (GROUP BY): You don't count them all in one big pile. Instead, you push them into separate color piles: a red pile, a green pile, and a yellow pile.
- The Counter (COUNT): You count the candies in each separate pile.
- The Pile Filter (HAVING): Now, you set a filter rule for your piles: "Only show me piles that contain more than 10 candies." You throw away the small yellow pile, leaving only red and green.
In SQL, this is exactly what GROUP BY and HAVING do!
GROUP BYsplits your rows into sub-piles.HAVINGfilters those sub-piles after they have been counted or summarized!
🔍 WHERE vs. HAVING: The Ultimate Difference
10,000 Candies
|
v
+----------------------+
| WHERE Clause | <--- Filters out broken candies (early row sifting)
+----------------------+
|
(Clean Candies Only)
|
v
+----------------------+
| GROUP BY color | <--- Creates color piles
+----------------------+
|
(Red, Green, Blue piles)
|
v
+----------------------+
| HAVING Count > 10 | <--- Discards small piles
+----------------------+
|
v
Output Groups
WHERE(Row Filter): Filters individual rows before they are grouped into piles. (e.g., "Throw away all broken candies first.")HAVING(Pile Filter): Filters the grouped piles after aggregate calculations have occurred. (e.g., "Only keep piles with > 10 candies.")
Order of Execution (Under the Hood)
FROM: Load target table.WHERE: Filter individual rows.GROUP BY: Group remaining rows into subsets.HAVING: Filter subsets based on aggregations.SELECT: Pick target columns and run formulas.ORDER BY: Sort output rows.LIMIT: Truncate list size.
💻 Code Examples
Let's group items by category and find categories with high average prices.
SQL Query
-- Group products by category, calculating average price,
-- but only return categories where average price is higher than $10
SELECT category, AVG(price) AS avg_price, COUNT(id) AS total_items
FROM products
WHERE status = 'available' -- Filters individual rows first
GROUP BY category
HAVING AVG(price) > 10.00; -- Filters groups after calculationMulti-Language Execution
⚠️ Common Mistakes
1. Putting aggregations in WHERE
Trying to run SELECT category FROM products WHERE AVG(price) > 10.00. This fails because WHERE acts on single rows before they are grouped!
- Bad:
SELECT category FROM products WHERE COUNT(id) > 5 GROUP BY category; - Good:
SELECT category FROM products GROUP BY category HAVING COUNT(id) > 5;
2. Missing Columns in GROUP BY
Selecting non-aggregated columns that are not included in the GROUP BY clause (e.g. SELECT category, item_name, SUM(price) ... GROUP BY category). The database doesn't know which item_name to show for the category group.
🔍 Interview Corner
Q1: What is the difference between WHERE and HAVING?
- WHERE is used to filter individual rows before any grouping or aggregate calculations take place.
- HAVING is used to filter grouped rows (summaries) after the
GROUP BYclause has completed. (It requires aggregate functions).
Q2: Why can't we use column aliases in the HAVING clause in some RDBMS?
Under the standard SQL order of operations, the HAVING clause is evaluated before the SELECT clause. Since column aliases are defined in SELECT, they do not exist yet when HAVING runs. (Note: some modern databases like MySQL allow it as an extension, but PostgreSQL and SQL Server strictly forbid it).
📝 Practice Links
Explore related coding challenges on the platform:
📝 Summary
GROUP BYorganizes query results into summarized categories.HAVINGacts as a filter on grouped categories (requires aggregate functions likeSUM,AVG, etc.).- Use
WHEREto filter rows first, thenGROUP BYto create piles, thenHAVINGto filter those piles.