SQL Server Summing count of multiple columns

kdfy810k 于 2023-10-15 发布在其他

关注(0)|答案(1)|浏览(104)

What do I need to change in the SQL to sum the msr_cnt field grouped into project_ref?

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
where       'msr_cnt' <> '0'
--group by  m.project_ref

The current output is

project_ref	msr_cnt
WHGSHDF	2
WHGSHDF	2
WHGSHDF	2
WHGSHDF	2
WHGSHDF	2
WHGSHDF	2
WHGSHDF	2

I need it to show

project_ref	msr_cnt
WHGSHDF	14

I just can't quite get it over the line.

sql-server

来源：https://stackoverflow.com/questions/77203839/summing-count-of-multiple-columns

1条答案

按热度按时间

am46iovg1#

Very difficult to answer without a proper set of source data, MRE etc, but I would have thought this should work:

select
    m.project_ref,
    sum (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
where msr_cnt <> '0'
group by m.project_ref

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server Summing count of multiple columns

1条答案

相关问题

热门标签

最新问答