HAVING

Filter groups after aggregation — the WHERE for aggregated results

Intermediate 12 minHAVINGGROUP BYfiltering

Filtering Groups

HAVING filters groups after aggregation is complete. It's the GROUP BY counterpart of WHERE — WHERE filters individual rows before grouping, HAVING filters the aggregated results after grouping finishes. Here's the distinction:

Aggregate thresholds — "Customers who placed more than 2 orders" can't go in WHERE — the count doesn't exist until after GROUP BY runs. Use HAVING COUNT(*) > 2 to filter groups based on their aggregated values.

WHERE vs HAVING — WHERE operates on raw row-level data before any grouping occurs. HAVING operates on aggregate values after groups are formed. Think of it as: WHERE decides which rows enter the groups, HAVING decides which groups appear in the output.

Combine both — You can use WHERE and HAVING in the same query. WHERE filters the input rows first, GROUP BY groups the survivors, then HAVING filters the resulting groups. Example: "Among delivered orders (WHERE), find customers who spent over 200 (HAVING)."

Knowledge Canvas

How HAVING Works

Filter groups after aggregation

HAVING is WHERE for groups — runs after GROUP BY
Can reference aggregate functions: COUNT, SUM, AVG, etc.
Filters entire groups, not individual rows
Always paired with GROUP BY (logically)

WHERE vs HAVING

Different filters, different stages

WHERE: before grouping

HAVING: after grouping

Filters rows

Filters groups

No aggregates allowed

Aggregates required/expected

WHERE price > 50

HAVING SUM(price) > 1000

HAVING Gotchas

Common mistakes

HAVING without GROUP BY treats the whole table as one implicit group — legal, just uncommon
Put row filters in WHERE, not HAVING — faster
HAVING can't use SELECT aliases in all databases
HAVING COUNT(*) > 1 is the classic "find duplicates" pattern

HAVING Patterns

Real-world uses

Find duplicates: HAVING COUNT(*) > 1
High-value groups: HAVING SUM(total) > 1000
Active users: HAVING COUNT(order_id) >= 5
Quality filter: HAVING AVG(rating) >= 4.0

Syntax

Syntax Template

1SELECT

2 group_col,

3 AGG(col)

4FROM table

5WHERE row_condition

6GROUP BY

7 group_col

8HAVING AGG(col) condition;

HAVINGFilters groups based on aggregate values

WHERE vs HAVINGWHERE filters rows; HAVING filters groups

Sample Data

orders

12 rows

customer_id	total
C1001	59.98
C1002	449
C1001	12.5
C1003	89.99
C1006	245
C1005	159
C1004	45.5
C1009	79
C1012	425
C1007	18.99
C1008	134
C1010	65.5

reviews

12 rows

id	product_id	rating
7001	P101	5
7002	P101	4
7003	P101	3
7004	P102	5
7005	P102	4
7006	P103	4
7007	P104	5
7008	P105	5
7009	P105	4
7010	P107	3
7011	P108	5
7012	P109	5

Worked Example

Find customers who have spent more than 100 in total across all their orders.

SQL

1SELECT

2 customer_id,

3 COUNT(*) AS order_count,

4 ROUND(SUM(total), 2) AS total_spent

5FROM orders

6GROUP BY

7 customer_id

8HAVING SUM(total) > 100

9ORDER BY

10 total_spent DESC;

First, orders are grouped by customer_id. Then SUM(total) is computed for each group. Finally, HAVING keeps only groups where the sum exceeds 100. Five customers meet this threshold; the other six (with totals from 18.99 up to 89.99) are excluded. We wrap SUM in ROUND to avoid floating-point artifacts.

Output

5 rows

customer_id	order_count	total_spent
C1002	1	449
C1012	1	425
C1006	1	245
C1005	1	159
C1008	1	134

Key Concepts

1WHERE = row filter (before grouping), HAVING = group filter (after)

2HAVING operates on aggregate values: HAVING COUNT(*) > 5

3You can use WHERE and HAVING in the same query

4HAVING without GROUP BY filters the single aggregate result

Pro Tip

Most analytical questions involve thresholds on aggregated values: "categories with more than 5 products," "customers who spent over 1000."

When to Use

High-value customer segments, categories worth investing in, underperforming regions, products with consistently high ratings.

Challenge

Solve the problem below

Find products that have received 2 or more reviews from the reviews table. Show the product_id, review count, and average rating. Round the average to 1 decimal place.

Your Query

GROUP BY

NULLs in Aggregations