luisarcher - 4 months ago 28

SQL Question

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 !

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

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.