luisarcher luisarcher - 28 days ago 7
SQL Question

SQL Server - Show All possible Values and Count Them

I have the following table:

IdSce Year NoIte Value
1 0 1 1
1 0 2 5
1 0 3 1
1 1 1 2
1 1 2 3
1 1 3 2
2 0 1 4
2 0 2 4
2 0 3 1
2 1 1 2
2 1 2 4
2 1 3 3


I want to group by IdSce and Year, and show each possible value and count how many time each value appears like this:

IdSce Year Value1 Value2 Value3 Value4 Value5
1 0 2 0 0 0 1
1 1 0 2 1 0 0
2 0 1 0 0 2 0
2 1 0 1 1 1 0


Thanks !

EDIT

shawnt00 is really close to what I want, but I'm looking to do it as dynamic as possible, meaning if I have 10 different values for the column value, I will be missing information in my table. Therefore, if I have 10 different values, I want 10 new columns (value1, value2, ... , value10)

This is what I've tried so far:

SELECT IdSce
,Year
,SUM(CASE WHEN Value >= 0 and Value < 1 THEN 1 else 0 end) Zero
,SUM(CASE WHEN Value >= 1 and Value < 2 THEN 1 else 0 end) One
,SUM(CASE WHEN Value >= 2 and Value < 3 THEN 1 else 0 end) Two
,SUM(CASE WHEN Value >= 3 and Value < 4 THEN 1 else 0 end) Three
,SUM(CASE WHEN Value >= 4 and Value < 5 THEN 1 else 0 end) Four
,SUM(CASE WHEN Value >= 5 THEN 1 else 0 end) FiveMore
,SUM(CASE WHEN Value >= 0 THEN 1 else 0 end) Total
FROM Table
GROUP BY IdSce
,Year


Thanks for the help again!

Answer

Ok, I'll do it!

select IdSce, "Year"
    count(case when Value = 1 then 1 end) as "1",
    count(case when Value = 2 then 1 end) as "2",
    count(case when Value = 3 then 1 end) as "3",
    count(case when Value = 4 then 1 end) as "4",
    count(case when Value = 5 then 1 end) as "5"
from T
group by IdSce, "Year"

I think you'll often find this filed under "conditional aggregation". SQL Server has a proprietary syntax that uses pivot if you want to look into that also.

Comments