HAVING with aliases

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.

Exercise HAVING and aliases

Write SUM(price) > 100 in HAVING instead of an alias.

Show example solution

SELECT category, SUM(price) AS total
FROM products
GROUP BY category
HAVING SUM(price) > 100;

Test yourself

Add the aggregate

HAVING ____(price) > 100;

Show answer

HAVING SUM(price) > 100;

HAVING with aliases: match the terms

alias

alias
SUM

sum
HAVING

group filter
portable

portable
Do aliases always work in HAVING?
- In some databases
- Always
- Never
- Only in MySQL
Show answer

In some databases — Aliases work in some databases; aggregates always work.

Next lesson: Summary - HAVING

We Are Enhancing Your Experience

Cookie Preferences

Strictly Necessary Cookies

Analytical Cookies