我有一个正在读取的临时表,我想查看只有两个等级值的列之一 - 3 或 4,并构建两个新列 - 一个保存 3 的计数和另一个用于保存 4 的计数 - 按特定分组。我的代码看起来像这样。
Select Max(Counting) as Total
, student
, stdType
, Score3 = (SELECT count(*) from #tempBWMSHonors3 where score = '3')
, Score4 = (SELECT count(*) from #tempBWMSHonors3 where score = '4')
from #tempBWMSHonors3
group by student, stateID, grade, stdType
I also embedded an image that show an example of my temp table and what I'm trying to achieve (as well as the result I am getting - but don't want).
[]
我认为你只想要条件聚合,而不是子查询:
select Max(Counting) as Total, student, stdType,
sum(case when score = '3' then 1 else 0 end) as Score3,
sum(case when score = '4' then 1 else 0 end) as Score4
from #tempBWMSHonors3
group by student, stdType;
注意:如果分数是数字而不是字符串,则不应使用单引号作为常量。
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)