[Solved] difficulty giving aliases inside case statement

by


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

Update: combining all (and use a subquery to simplify pct calculation):

select 
    storeId, 
    district,
    region,
    via_u,
    via_p,
    via_u/100./(via_u+via_p) as via_u_pct
from (
    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
    ) Step1

13

solved difficulty giving aliases inside case statement