2

I have a table of over 1 million test score records that basically have a unique score_ID, a subject_ID and a score given by a person. The score range for most subjects is 0-3, but some have a range of 0-4. There are about 25 possible subjects.

I need to produce a score distribution report which looks like:

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

So it groups the data by subject_ID, then shows how many times a specific score value was given within that subject.

Any SQL pointers to generate this would be greatly appreciated.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Bryan Lewis
  • 5,629
  • 4
  • 39
  • 45

1 Answers1

6
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
Thomas
  • 63,911
  • 12
  • 95
  • 141