如何在SQL中按指定字段分组统计非空字段数量总和？

需求说明 #

需要针对每行数据，统计指定字段中的非空值数量，最终输出每个project_ref对应的非空字段计数msr_cnt。

数据示例 #

原始数据每行包含project_ref字段，以及EWI/IWI、Glazing、Solar、CWI、Boiler、TRV、LI、RIRI、UFI、ASHP共10个业务字段，部分字段为空字符串。

期望输出 #

输出结果包含两列：project_ref（项目编号）和msr_cnt（该行对应10个业务字段中的非空值总数）。

当前使用的SQL代码 #

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

代码验证与补充方案 #

你的写法是完全可行的：通过VALUES子句将每行的多个横向字段转换为纵向的临时数据集，再用COUNT(*)统计其中非空（v.col <> ''）的行数，正好对应每行的非空字段数量。

如果需要兼容更多场景，可参考以下补充方案：

1. 处理NULL值场景：如果字段可能为NULL而非空字符串，需调整过滤条件，避免漏统计：

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 IS NOT NULL AND v.col <> ''
        ) as 'msr_cnt'
from        SMSDB1.dbo.ops_measure m

2. CASE表达式累加写法：逻辑更直观，兼容不支持VALUES行集构造的旧版SQL Server：

select 
    m.project_ref,
    (
        CASE WHEN m.[EWI/IWI] <> '' THEN 1 ELSE 0 END +
        CASE WHEN m.Glazing <> '' THEN 1 ELSE 0 END +
        CASE WHEN m.Solar <> '' THEN 1 ELSE 0 END +
        CASE WHEN m.CWI <> '' THEN 1 ELSE 0 END +
        CASE WHEN m.Boiler <> '' THEN 1 ELSE 0 END +
        CASE WHEN m.TRV <> '' THEN 1 ELSE 0 END +
        CASE WHEN m.LI <> '' THEN 1 ELSE 0 END +
        CASE WHEN m.RIRI <> '' THEN 1 ELSE 0 END +
        CASE WHEN m.UFI <> '' THEN 1 ELSE 0 END +
        CASE WHEN m.ASHP <> '' THEN 1 ELSE 0 END
    ) as msr_cnt
from SMSDB1.dbo.ops_measure m

内容的提问来源于stack exchange，提问作者Ian