SQL sum the count of populated fields grouped by another field

How do I sum the count of populated fields.

An example of the data.

enter image description here

The desired output would be.

enter image description here

This is what I have managed so far.

select      m.project_ref,
        (   select count(*)
            from (values (m.[EWI/IWI]), (m.Glazing), (m.Solar), (m.CWI), (m.Boiler), (m.TRV), (m.LI), (m.RIRI), (m.UFI), (m.ASHP)) as v(col)
            where v.col <> ''
        ) as 'msr_cnt'
from        SMSDB1.dbo.ops_measure m

Thank you

>Solution :

This can be done by using the conditional aggregation :

select project_ref, sum(case when EWI_IWI <> '' then 1 else 0 end )
                    + sum(case when Glazing <> '' then 1 else 0 end ) 
                    + ... as msr_count
from mytable
group by project_ref

Leave a Reply