How do I sum the count of populated fields.
An example of the data.
The desired output would be.
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
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