SQL如何基于组中现有列的计数为新列创建值？ [英] SQL How to create a value for a new column based on the count of an existing column by groups?

问题描述

我有一个要读取的临时表，我想看一看只有两个等级值的列之一（3或4）并建立两个新列-其中一个列保存了3和另一个保存4的计数-通过特定的分组。我的代码看起来像这样。

I have a temp table that I'm reading from, and I want to look at one of the columns that only has two grade values - either a 3 or a 4 and build two new columns - one that holds the count of the 3's and the other to hold the count of the 4's - by a particular grouping. My code looks like this.

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). []

推荐答案

我认为您只需要条件聚合，而不是子查询：

I think you just want conditional aggregation, not subqueries:

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;

注意：如果score是数字而不是字符串，则不应使用单引号常数。

Note: if score is a number and not a string, then you should not use single quotes for the constant.

这篇关于SQL如何基于组中现有列的计数为新列创建值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！