如何在 SQL Server 的 case 语句中执行 SUM() [英] How to do a SUM() inside a case statement in SQL server

问题描述

我想在我的 case 语句中添加一些计算来动态创建新列的内容，但出现错误:

I want to add some calculation inside my case statement to dynamically create the contents of a new column but I get the error:

选择列表中的列Test1.qrank"无效，因为它既不包含在聚合函数中也不包含在 GROUP BY 子句中.

这是我正在处理的代码

case 
    when test1.TotalType = 'Average' then Test2.avgscore
    when test1.TotalType = 'PercentOfTot' then (cnt/SUM(test1.qrank))
    else cnt
end as displayscore

我确实尝试过分组，但没有成功.

I did try to group but it didn't work.

有什么提示吗?

推荐答案

当您在 GROUP BY 语句中使用子句而不将其包含在选择中时，可能会发生您发布的错误.

The error you posted can happen when you're using a clause in the GROUP BY statement without including it in the select.

示例

这个有效！

     SELECT t.device,
            SUM(case when transits.direction = 1 then 1 else 0 end) ,
            SUM(case when transits.direction = 0 then 1 else 0 end) from t1 t 
            where t.device in ('A','B') group by t.device

这个没有(从选择中省略了t.device)

This one not (omitted t.device from the select)

     SELECT 
            SUM(case when transits.direction = 1 then 1 else 0 end) ,
            SUM(case when transits.direction = 0 then 1 else 0 end) from t1 t 
            where t.device in ('A','B') group by t.device

这将产生您的错误，抱怨我正在为未包含在选择中的内容分组

This will produce your error complaining that I'm grouping for something that is not included in the select

请提供所有查询以获得更多支持.

Please, provide all the query to get more support.

这篇关于如何在 SQL Server 的 case 语句中执行 SUM()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！