3

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). [enter image description here]

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Karen
  • 157
  • 2
  • 12

1 Answers1

4

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;

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

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Yes it is thank you. This works perfectly. I believe I've been looking at it too long and trying to make it more difficult. Thank you so much! – Karen Dec 08 '15 at 21:30