Search code examples
sqlsql-server-2008having

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

My code is like shown below :

select col1,count(col2) as col7
from --some join operation
group by col1
having col7 >= 3 -- replace col7 by count(col2) to make the code work

My code causes the error "Invalid column name 'col7' ". Why does this happen ? It seems illogical that SQL does not allow me to use col7 in the last line.

I am using SQL server express 2008

Solution

  • In MS SQL, the only place (I'm aware of) that you can reference aliases is in the ORDER BY clause. The ability to reference aliases in other parts of the query is a feature that many other db platforms have and honestly it annoys me that Microsoft hasn't considered it a useful enough feature to add it.