SQL query to get field value distribution
- by Bryan Lewis
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.