Sql Count. How Can I Count How Many Distinct Values Are In A Table When An Other Two Columns Are Matching?

I am trying to complete an sql query to show how many GCSEs a student has on record.] *STUDENT *SUBJECT *SCHOOL ABB13778 | Engl

Solution 1:

After grouping the data by school and student, you need to then run it through a PIVOT on the count of Students with each number of subjects, to get the histogram 'bins':

SELECT [School], [0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11]FROM
(
   SELECT School, Student, COUNT([Subject]) AS Subjects
   FROM Student_GCSE
   GROUP BY School, Student
) x
PIVOT
(
  COUNT(Student)
  FOR Subjects IN ([0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11])
) y;

SqlFiddle here

I've assumed a finite number of subjects, but you can derive the columns as well using dynamic sql

Solution 2:

Group by should solve this, Something like following:

select SCHOOL, subject, count(*) as NUM_STUDENTS from records
groupby STUDENT, SCHOOL;

Solution 3:

Now, I don't use SQL Server and I don't have a SQL command line handy, but have you tried something like this:

SELECT SCHOOL, N, COUNT(STUDENT) 
FROM (SELECT SCHOOL, STUDENT, COUNT(DISTINCT SUBJECT) AS N 
FROM MY_TABLE GROUPBY SCHOOL, STUDENT) GROUPBY SCHOOL, N;