difficulty giving aliases inside case statement-CodePudding

I have a query which is giving me the output as shown in the screenshot below.

select 
    a.storeId, b.district, b.region,
    count (case when a.photoAppImage is null then 1 end) as via_u,
    count (case when a.photoAppImage =1 then 1 end) as via_p
from 
    used_listings_V2 a
left outer join 
    locations b on a.storeId = b.storeID 
where 
    convert(date, a.imageUpdateDate) = convert(date, GETDATE()-1)
group by 
    a.storeId, b.district, b.region

edited

I wanted to split the counts in two different columns that I am getting in a single row. All the records having null photoAppImage should have the count displayed under “Via_u” and the records having 1 photoAppImage should have the count displayed under “Via_p”. The expected output will look this:

Basically I think I am having difficulty giving aliases inside case statement. Can somebody please help me on this?

CodePudding user response：

Use:

Count(case when a.photoappimage is null then 1 end) as via_u,
Count(case when a.photoappimage is null then 1 end)/100. as via_u_pct,
Count(case when a.photoappimage=1 then 1 end ) as via_p,
Count(case when a.photoAppImage is null or a.photoAppImage=1 then 1 end) as via_u_or_p