I want records with clearence rate > 75. I can't use this in where clause, how should I do it?

user_19857049 0
Viewed 28
Select
suburb,round(100*count(case when result like 'Sold%' Then 1 else null end)/count(*)) 
AS clearence_rate
from sales 
group by suburb
order by suburb;
sql
2 Answers

If you want to filter the result of an aggregate expression you would do

Select suburb, <aggregate expression> AS clearence_rate
from sales 
group by suburb
having  <aggregate expression> > 75
order by suburb;
user_153326500

There 2 opotion available. First using CTE:

;WITH CTE AS (
Select
suburb,round(100*count(case when result like 'Sold%' Then 1 else null end)/count(*))   as [value]
AS clearence_rate
from sales 
group by suburb
order by suburb
)
Select * from CTE Where [value] > 75

Second option using HAVING cluase:

Select
suburb,round(100*count(case when result like 'Sold%' Then 1 else null end)/count(*))   as [value]
AS clearence_rate
from sales 
group by suburb
HAVING round(100*count(case when result like 'Sold%' Then 1 else null end)/count(*))   > 75
user_62032110
Related