What is HAVING?
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
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
SELECT column, COUNT(*)
FROM table
GROUP BY column
HAVING COUNT(*) > 5;Simple Examples
Example 1: Grades with Many Students
SELECT grade, COUNT(*) AS total
FROM students
GROUP BY grade
HAVING COUNT(*) >= 3;Shows only grades with 3+ students.
Example 2: High Spending Customers
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
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
- Use WHERE to filter rows (before grouping)
- Use HAVING to filter groups (after grouping)
- HAVING needs GROUP BY, WHERE doesn't
Summary
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
Congratulations! You have completed grouping and filtering groups. Next, you will learn about Data Constraints to ensure data integrity.
Practice 1
Count students per grade
Source Table: students
| id | name | age | grade |
|---|---|---|---|
| 1 | Alice | 20 | A |
| 2 | Bob | 22 | B |
| 3 | Charlie | 21 | A |
| 4 | David | 23 | B |
4 rows
SQL Editor
Loading...
Practice 2
Grades with 2+ students
Source Table: students
| id | name | age | grade |
|---|---|---|---|
| 1 | Alice | 20 | A |
| 2 | Bob | 22 | B |
| 3 | Charlie | 21 | A |
| 4 | David | 23 | B |
4 rows
SQL Editor
Loading...
Practice 3
Grades with avg age > 21
Source Table: students
| id | name | age | grade |
|---|---|---|---|
| 1 | Alice | 20 | A |
| 2 | Bob | 22 | B |
| 3 | Charlie | 21 | A |
| 4 | David | 23 | B |
4 rows
SQL Editor
Loading...
Practice 4
Combine all: COUNT, AVG, HAVING
Source Table: students
| id | name | age | grade |
|---|---|---|---|
| 1 | Alice | 20 | A |
| 2 | Bob | 22 | B |
| 3 | Charlie | 21 | A |
| 4 | David | 23 | B |
4 rows
SQL Editor
Loading...