SQL Server SQL sum the count of populated fields grouped by another field

omvjsjqw 于 12个月前发布在其他

关注(0)|答案(2)|浏览(134)

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

Thank you

sql-server

来源：https://stackoverflow.com/questions/77216139/sql-sum-the-count-of-populated-fields-grouped-by-another-field

2条答案

按热度按时间

mfuanj7w1#

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

赞(0）回复(0）举报 12个月前

ldfqzlk82#

It is not entirely clear from the data provide how you are calculating msr_count, but a simple GROUP BY might be the answer here:

SELECT
   project_ref
  ,count(*)  msr_count
 from  SMSDB1.dbo.ops_measure
 group by
   project_ref

赞(0）回复(0）举报 12个月前

我来回答

SQL Server SQL sum the count of populated fields grouped by another field

2条答案

相关问题

热门标签

最新问答