HAVING with aliases
- SQL course
- Intermediate
- Lesson 34/115
- 4 min
Alias an aggregate and use it in HAVING.
HAVING and aliases
Some databases let you use an alias in HAVING. Standard SQL repeats the aggregate.
SELECT category, SUM(price) AS total
FROM products
GROUP BY category
HAVING SUM(price) > 100;Put the alias in SELECT, the aggregate in HAVING.
category, SUM(price) AS total is selected from products. Rows are grouped by category; aggregate functions are computed per group. HAVING SUM(price) > 100 runs after grouping and drops groups that fail the condition.
HAVING and aliases — details
PostgreSQL and MySQL usually allow aliases. For portability, write the aggregate explicitly.
For example, HAVING total > 500 works in some databases but isn't standard; HAVING SUM(amount) > 500 works everywhere.
Practice HAVING and aliases
Write SUM(price) > 100 in HAVING instead of an alias.
Starter code
-- portable having
SELECT category, SUM(price) AS total
FROM products
GROUP BY category
HAVINGSample solution
SELECT category, SUM(price) AS total
FROM products
GROUP BY category
HAVING SUM(price) > 100;Expected output
category total Elektronik 350 Kitap 150
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 Add the aggregate
HAVING ____(price) > 100;HAVING SUM(price) > 100;Explanation: HAVING uses an aggregate; SUM(price) > 100 keeps groups whose total is above 100.
2 HAVING with aliases: match the terms
| Term | Meaning |
|---|---|
alias |
alias |
SUM |
sum |
HAVING |
group filter |
portable |
portable |
3 Do aliases always work in HAVING?
- In some databases Correct answer
- Always
- Never
- Only in MySQL
Explanation: Aliases work in some databases; aggregates always work.