SQL query to get the distribution of field values

Question

I have a table with more than 1 million test entries, which basically have a unique score_ID, subject_ID, and a human grade. The rating range for most items is 0-3, but some of them have a range of 0-4. There are about 25 possible items.

I need to create a score distribution report that looks like this:

subject_ID     0    1    2    3    4
----------    ---  ---  ---  ---  ---
1             967  576  856  234  
2             576  947  847  987  324
.
.

Thus, it groups the data by subject_ID, and then shows how many times a certain rating value has been set in this subject.

Any SQL pointers to generate this would be greatly appreciated.

Answer 1

Select subject_id
    , Sum( Case When Score = 0 Then 1 Else 0 End ) As [0]
    , Sum( Case When Score = 1 Then 1 Else 0 End ) As [1]
    , Sum( Case When Score = 2 Then 1 Else 0 End ) As [2]
    , Sum( Case When Score = 3 Then 1 Else 0 End ) As [3]
    , Sum( Case When Score = 4 Then 1 Else 0 End ) As [4]
From Table
Group By subject_id