CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
CodeNinja
CodeNinja

Reputation: 3278

Referencing an aliased expression in the WHERE clause

I have the following query and I would like to use a where clause on the expression Percentage which contains an aggregate:

SELECT Percentage = CONVERT(DECIMAL(10,1),100 - COUNT(some_irrelevant_column))
FROM Product P INNER JOIN Item PD
ON PD.ProductId = P.ProductId
WHERE Percentage < 50;

This yields the following error:

Invalid column name 'Percentage'.

Upvotes: 0

Views: 175

Answers (4)

Declan_K
Declan_K

Reputation: 6826

To filter a result set based on an aggregate function, you need to include it in the HAVING clause, not in the WHERE clause..

Upvotes: 2

Aaron Bertrand
Aaron Bertrand

Reputation: 280590

The problem is that both the WHERE and HAVING clauses are parsed before the SELECT list. This has nothing to do with aggregates. The same thing will happen if you have a very simple expression, such as:

SELECT a = 1 + 2 WHERE a = 3;

Picture that SQL Server actually reads that backwards: "For the rows where a = 3, return the expression 1 + 2, and label it a." This doesn't work because a doesn't exist yet in order to check if a = 3. I talk a little bit about why this is on this answer and this answer over on dba.stackexchange.com (there are some other answers there worth reading, as well).

Therefore you cannot create an alias in the SELECT list and then reference the alias in other clauses (ORDER BY is the only one where this works, and even there you will find exceptions). The workarounds are to use a subquery or CTE:

SELECT a FROM (SELECT 1 + 2) AS x(a) WHERE a = 3;

;WITH x(a) AS (SELECT 1 + 2) SELECT a FROM x WHERE a = 3;

Or repeat the expression (usually not desirable):

SELECT a = 1 + 2 WHERE 1 + 2 = 3;

Upvotes: 4

roman
roman

Reputation: 117550

You can use Common Table Expression:

with cte as (
    select CONVERT(DECIMAL(10,1),100 - (CAST(COUNT(DISTINCT case when PD.ExceptionCode  != ' ' then PD.Id  END) as float)/CAST(COUNT(PD.Id) as float)*100)) as Percentage
from Product as P
    inner join Item as PD on PD.ProductId = P.ProductId
)
select Percentage from cte where Percentage < 50

It's possible to use subquery, but for me CTE is more readable

select *
from (
    select CONVERT(DECIMAL(10,1),100 - (CAST(COUNT(DISTINCT case when PD.ExceptionCode  != ' ' then PD.Id  END) as float)/CAST(COUNT(PD.Id) as float)*100)) as Percentage
from Product as P
    inner join Item as PD on PD.ProductId = P.ProductId
) as A
where A.Percentage < 50

It also possible to solve this with having, but it would not be very readable or maintainable:

select CONVERT(DECIMAL(10,1),100 - (CAST(COUNT(DISTINCT case when PD.ExceptionCode != ' ' then PD.Id  END) as float)/CAST(COUNT(PD.Id) as float)*100)) as Percentage
from Product as P
    inner join Item as PD on PD.ProductId = P.ProductId
having CONVERT(DECIMAL(10,1),100 - (CAST(COUNT(DISTINCT case when PD.ExceptionCode != ' ' then PD.Id  END) as float)/CAST(COUNT(PD.Id) as float)*100)) < 50

Upvotes: 5

Sonam
Sonam

Reputation: 3466

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference

Upvotes: 0

Related Questions