HAVING with aggregates
- SQL course
- Intermediate
- Lesson 32/115
- 4 min
Filter groups by COUNT or SUM.
HAVING with aggregates
With HAVING you test aggregates like SUM, AVG, MIN, MAX. It sets a condition per group.
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 500;This query returns customers who spent over 500.
customer_id, SUM(amount) AS total is selected from orders. Rows are grouped by customer_id; aggregate functions are computed per group. HAVING SUM(amount) > 500 runs after grouping and drops groups that fail the condition.
HAVING with aggregates — details
Using an aggregate function in HAVING is essential. Otherwise WHERE would suffice.
A condition on the group column (e.g. category = 'Kitap') is more efficient in WHERE; save HAVING for aggregates such as COUNT and SUM.
Practice HAVING with aggregates
Compute per-customer totals with SUM(amount) and keep those over 500 with HAVING.
Starter code
-- total filter
SELECT customer_id,Sample solution
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 500;Expected output
customer_id total 1 750 3 600
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 HAVING with aggregates: match the terms
| Term | Meaning |
|---|---|
SUM |
sum |
AVG |
average |
MIN |
minimum |
MAX |
maximum |
2 Add SUM
SELECT customer_id, ____(amount) AS total
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 500;SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 500;Explanation: SUM(amount) computes each customer's total; HAVING keeps those above 500.
3 What is used in HAVING?
- The column name
- JOIN
- An aggregate function Correct answer
- LIMIT
Explanation: An aggregate function is used in HAVING.