What is HAVING?
Sample Students Table
| id | name | age | grade |
|---|---|---|---|
| 1 | John | 20 | A |
| 2 | Mary | 19 | A |
| 3 | Peter | 21 | A |
| 4 | Sarah | 20 | B |
| 5 | Tom | 22 | B |
| 6 | Lisa | 18 | C |
6 rows
WHERE vs HAVING Comparison
| Aspect | WHERE | HAVING |
|---|---|---|
| Purpose | Filter rows | Filter groups |
| When | Before GROUP BY | After GROUP BY |
| Works on | Individual rows | Grouped results |
| Can use aggregates | No | Yes |
| Example | WHERE age > 18 | HAVING COUNT(*) > 5 |
5 rows
HAVING filters groups created by GROUP BY. It's like GROUP BY creates categories, and HAVING picks which categories to show.
Simple example: You group students by grade (A, B, C). HAVING shows only grades with 3 or more students.
WHERE vs HAVING
Sample Students Table
| id | name | age | grade |
|---|---|---|---|
| 1 | John | 20 | A |
| 2 | Mary | 19 | A |
| 3 | Peter | 21 | A |
| 4 | Sarah | 20 | B |
| 5 | Tom | 22 | B |
| 6 | Lisa | 18 | C |
6 rows
WHERE vs HAVING Comparison
| Aspect | WHERE | HAVING |
|---|---|---|
| Purpose | Filter rows | Filter groups |
| When | Before GROUP BY | After GROUP BY |
| Works on | Individual rows | Grouped results |
| Can use aggregates | No | Yes |
| Example | WHERE age > 18 | HAVING COUNT(*) > 5 |
5 rows
WHERE - Filters individual rows BEFORE grouping HAVING - Filters groups AFTER grouping
Think of it like:
- WHERE: "Pick only ripe apples" → Then group by color
- HAVING: Group all apples by color → "Show only colors with 10+ apples"
Basic Syntax
Sample Students Table
| id | name | age | grade |
|---|---|---|---|
| 1 | John | 20 | A |
| 2 | Mary | 19 | A |
| 3 | Peter | 21 | A |
| 4 | Sarah | 20 | B |
| 5 | Tom | 22 | B |
| 6 | Lisa | 18 | C |
6 rows
WHERE vs HAVING Comparison
| Aspect | WHERE | HAVING |
|---|---|---|
| Purpose | Filter rows | Filter groups |
| When | Before GROUP BY | After GROUP BY |
| Works on | Individual rows | Grouped results |
| Can use aggregates | No | Yes |
| Example | WHERE age > 18 | HAVING COUNT(*) > 5 |
5 rows
SELECT column, COUNT(*)
FROM table
GROUP BY column
HAVING COUNT(*) > 5;Simple Examples
Sample Students Table
| id | name | age | grade |
|---|---|---|---|
| 1 | John | 20 | A |
| 2 | Mary | 19 | A |
| 3 | Peter | 21 | A |
| 4 | Sarah | 20 | B |
| 5 | Tom | 22 | B |
| 6 | Lisa | 18 | C |
6 rows
WHERE vs HAVING Comparison
| Aspect | WHERE | HAVING |
|---|---|---|
| Purpose | Filter rows | Filter groups |
| When | Before GROUP BY | After GROUP BY |
| Works on | Individual rows | Grouped results |
| Can use aggregates | No | Yes |
| Example | WHERE age > 18 | HAVING COUNT(*) > 5 |
5 rows
Example 1: Grades with Many Students
Result: Grades with 3+ Students (HAVING COUNT(*) >= 3)
| grade | student_count |
|---|---|
| A | 3 |
1 row
SELECT grade, COUNT(*) AS total
FROM students
GROUP BY grade
HAVING COUNT(*) >= 3;Shows only grades with 3+ students.
Example 2: High Spending Customers
Result: High Salary Departments
| department | avg_salary |
|---|---|
| Engineering | 85000 |
| Sales | 65000 |
2 rows
SELECT customer_id, SUM(total) AS spent
FROM orders
GROUP BY customer_id
HAVING SUM(total) > 1000;Shows only customers who spent over $1000.
Example 3: Both WHERE and HAVING
Result: Products with 100+ Sales
| product_name | total_sold |
|---|---|
| Mouse | 150 |
| Laptop | 125 |
2 rows
SELECT grade, COUNT(*) AS total
FROM students
WHERE age >= 18
GROUP BY grade
HAVING COUNT(*) >= 2;- WHERE picks students aged 18+
- GROUP BY groups them by grade
- HAVING shows only grades with 2+ students
Quick Rule
Sample Students Table
| id | name | age | grade |
|---|---|---|---|
| 1 | John | 20 | A |
| 2 | Mary | 19 | A |
| 3 | Peter | 21 | A |
| 4 | Sarah | 20 | B |
| 5 | Tom | 22 | B |
| 6 | Lisa | 18 | C |
6 rows
WHERE vs HAVING Comparison
| Aspect | WHERE | HAVING |
|---|---|---|
| Purpose | Filter rows | Filter groups |
| When | Before GROUP BY | After GROUP BY |
| Works on | Individual rows | Grouped results |
| Can use aggregates | No | Yes |
| Example | WHERE age > 18 | HAVING COUNT(*) > 5 |
5 rows
- Use WHERE to filter rows (before grouping)
- Use HAVING to filter groups (after grouping)
- HAVING needs GROUP BY, WHERE doesn't
Summary
Sample Students Table
| id | name | age | grade |
|---|---|---|---|
| 1 | John | 20 | A |
| 2 | Mary | 19 | A |
| 3 | Peter | 21 | A |
| 4 | Sarah | 20 | B |
| 5 | Tom | 22 | B |
| 6 | Lisa | 18 | C |
6 rows
WHERE vs HAVING Comparison
| Aspect | WHERE | HAVING |
|---|---|---|
| Purpose | Filter rows | Filter groups |
| When | Before GROUP BY | After GROUP BY |
| Works on | Individual rows | Grouped results |
| Can use aggregates | No | Yes |
| Example | WHERE age > 18 | HAVING COUNT(*) > 5 |
5 rows
Key points to remember:
- HAVING filters groups created by GROUP BY
- WHERE filters rows, HAVING filters groups
- HAVING must be used with GROUP BY
- Use HAVING for aggregate conditions
- Can combine multiple conditions with AND/OR
- Can use both WHERE and HAVING together
- WHERE executes before grouping
- HAVING executes after grouping
- HAVING can reference aggregate functions
- WHERE cannot reference aggregate functions
What Comes Next
Sample Students Table
| id | name | age | grade |
|---|---|---|---|
| 1 | John | 20 | A |
| 2 | Mary | 19 | A |
| 3 | Peter | 21 | A |
| 4 | Sarah | 20 | B |
| 5 | Tom | 22 | B |
| 6 | Lisa | 18 | C |
6 rows
WHERE vs HAVING Comparison
| Aspect | WHERE | HAVING |
|---|---|---|
| Purpose | Filter rows | Filter groups |
| When | Before GROUP BY | After GROUP BY |
| Works on | Individual rows | Grouped results |
| Can use aggregates | No | Yes |
| Example | WHERE age > 18 | HAVING COUNT(*) > 5 |
5 rows
Congratulations! You have completed grouping and filtering groups. Next, you will learn about Data Constraints to ensure data integrity.
SQL Editor
Loading...
Show grades with 2 or more students
Output
Click "Run Query" to see results
SQL Editor
Loading...
Grades with average age over 20
Output
Click "Run Query" to see results
SQL Editor
Loading...
WHERE and HAVING together
Output
Click "Run Query" to see results