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

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;

CodePudding user response：

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;

CodePudding user response：

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