CODEWITHSUNDEEP
sqlsql-servert-sql
James
James

Reputation: 63

Why is using an alias in the having clause working?

I am using a group by and having clause in my query and the having seems to be working fine using an alias. No matter what value I put in, or operator (<, >) it returns the correct result. According to logical query processing this should not work but it does. In addition, even if I put some meaningless string in the count function in the having clause it still works.

I am totally baffled!

use TSQL2014;
select
c.categoryname,
count(p.productid) as 'TotalProducts'
from Production.Products p

left join Production.Categories c
on p.categoryid = c.categoryid

group by c.categoryname

--having count(p.productid) > 10 
having count('aaaaaa') > 10

order by 'TotalProducts' desc;

Upvotes: 0

Views: 325

Answers (2)

Zohar Peled
Zohar Peled

Reputation: 82474

Assuming you are referring to the line currently commented out in the code you've posted - the p alias was used in the from clause - that means you can use it in any other clause in the query - including sub queries, having clause, and even apply clauses and join clauses.

This is not true when using an alias in the select clause - these aliases can only be used on the order by clause.

select
c.categoryname,
count(p.productid) as 'TotalProducts'
from Production.Products p

left join Production.Categories c

-- both `p` and `c` aliases are valid here since they where introduced in the `from` and `join` clauses
on p.categoryid = c.categoryid

-- the use of `c` here is valid since it was introduced in the `join` clause
group by c.categoryname

-- the use of `p` here is valid since `p` was introduced in the `from` clause.
having count(p.productid) > 10 

-- This, however, will cause an error - invalid column name `TotalProducts`
-- having count(TotalProducts) > 10 

-- This is valid since TotalProducts is an alias introduced in the `select` clause.
order by 'TotalProducts' desc;

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175736

Why is using an alias in the having clause working?

'aaaaaa' is not an alias but string literal. 

having count('aaaaaa') > 10
-- same as
       count(*)
       count(1) 
       count(GETDATE())

As long as expression isn't NULL then COUNT will work properly.

Upvotes: 2

Related Questions