Why can't I use an alias for an aggregate in a having clause?

sql

subqueries

common-table-expressions

sql-server

byAlex Kataev·Jan 15, 2025

The HAVING clause cannot recognize any alias created in SELECT because SQL evaluates the groups first before assigning aliases in SELECT. To avoid alias awareness issues, directly repeat the aggregate function in the HAVING clause:

SELECT employee_id, COUNT(*) AS total_sales -- No, not my actual total_sales, just an alias!
FROM sales
GROUP BY employee_id
HAVING COUNT(*) > 10; -- Hey! Who stole my total_sales? Ah, it's COUNT(*)'s showtime.

The alias total_sales is not recognized in the HAVING clause because it evaluates prior to the SELECT clause.

Unmasking SQL sequence of events

Understanding SQL event sequence is crucial. The usual order of SQL operations is: FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY. This means that the SQL aliases are not available until the SELECT operation, which comes after the HAVING clause.

Dealing with alias issues: Enter Subqueries

Facing a roadblock with alias referencing in the HAVING clause? Use subqueries to your rescue! Wrap your query within another query and declare aliases in the inner query:

SELECT employee_id, total_sales
FROM (
    SELECT employee_id, COUNT(*) AS total_sales
    FROM sales
    GROUP BY employee_id
) AS alias_aware_subquery -- Now that's what I call inception!
WHERE total_sales > 10; -- Subquery to the rescue!

Here, total_sales is assigned in the inner query, making it accessible in the outer query's WHERE clause.

Harnessing the power of database ecosystems

Remember, different database systems handle alias referencing differently. SQL Server is more restrictive and often requires a subquery or CTE, while MySQL and PostgreSQL are more lenient. So, getting to know your database's specificities can save you from pulling your hair out(actually helps in social distancing too!).

Visualisation

Let's visualize why aliases in a HAVING clause don't mix well using the world's favorite pastime: borrowing books!

- Library Rule (📜): "You can't borrow books using **pen names**; use **official authors' names only**."

Imagine SELECT as your library card (📄):

SELECT author AS pen_name, COUNT(book) AS total_books -- penned down!
FROM library
GROUP BY author
HAVING total_books > 5; -- What sorcery is this 'total_books'!?

The HAVING clause cannot comprehend total_books because it is evaluated right after counting but before updating information on your card:

- SQL Librarian (🤓): "I've tallied the counts, but **who is 'total_books'?** I only know the *official count*."

The solution: make your request explicit:

HAVING COUNT(book) > 5; -- Ah-ha! Now, that's understandable.

For the love of CTEs: Common Table Expressions

Common Table Expressions (CTEs) can be a godsend when dealing with aliases and aggregates. In reality, a CTE creates a temporary result set that you can refer to in a subsequent command:

WITH SalesCTE AS (
    SELECT employee_id, COUNT(*) AS total_sales -- being a little salesy here
    FROM sales
    GROUP BY employee_id
)
SELECT employee_id, total_sales
FROM SalesCTE
WHERE total_sales > 10; -- oh hello there, total_sales!

In this context, total_sales becomes a viable alias that can be freely utilized post CTE definition.

Stay on top of SQL Server enhancements

If SQL Server is your playground, it always pays to keep track of version updates. As newer versions may improve alias handling in the HAVING clause, you can vote to retire your workarounds and enjoy the simplified development.