SQL Count. How can I count how many distinct values are in a table when an other two columns are matching?

Question

I am trying to complete an sql query to show how many GCSEs a student has on record.]

    *STUDENT         *SUBJECT                                     *SCHOOL
    ABB13778 |  English                                   | Social Care & Early Years
    ABB13778 |  Information and Communication Technology  | Social Care & Early Years
    ABB13778 |  Mathematics                               | Social Care & Early Years
    ABB13778 |  Media Studies                             | Social Care & Early Years

For example this student should recieve a count of 4 as there is 4 distinct subjects assigned to the school and student ID.

I can count the items but the output should be by school and number(see below), and I am not sure how toy form a case to create this

                               NUM OF STUDENT with each amount of GCSE
   SCHOOL                      1   2   3   4   5   6   7   8   9   10   11

   Social Care & Early Years | 5   1   2   7   0   1   13  15  8   4     2
   Built Environment         |
   Business & Computing      |

This is probably simpler than I am thinking but at the minute I cant get my head around it. Any help would be greatly appreciated.

Have you looked at this question? http://stackoverflow.com/questions/2192330/select-countdistinct-value-returns-1?rq=1 — Ray Henry, Apr 23 '14 at 14:38
So you need the count for each student and then you want to know how many students have a given count (1 to ...) for each school? Did you want this in one SQL query? — Ray Henry, Apr 23 '14 at 14:43
Yes. it can be in a few query's but as it come from a large database it needs to be formatted in the query as it would be an extensive task. I can pull the data easily but its not in the format that I need @RayHenry — Seanin, Apr 23 '14 at 14:47

score 3 · Accepted Answer · edited May 23 '17 at 10:32

3

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

edited May 23 '17 at 10:32

Community

1
1

answered Apr 23 '14 at 14:46

StuartLC

104,537
17
209
285

+1 : Though this (by the nature of pivoted data) does assume that no-one has 12 or more GCSEs. – MatBailie Apr 23 '14 at 14:48
Yup, doing this dynamically is quite straightforward - added a link. – StuartLC Apr 23 '14 at 14:50
1

Depends on your definition of `straightforward` ;) Personally I always shudder at that code, too messy, and push pivoting to the application. – MatBailie Apr 23 '14 at 14:54
There is an error coming up saying the syntax is incorrect after x. This should work though my version of sql server management is 2005. would this be an issue. – Seanin Apr 23 '14 at 14:56
Hmm ... I've just run this on `Microsoft SQL Server 2005 - 9.00.5324.00 (X64)`. Possibly related to your actual table / column names? – StuartLC Apr 23 '14 at 15:00
Msg 325, Level 15, State 1, Line 15 Incorrect syntax near 'PIVOT'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel.... Does this mean anything to you? – Seanin Apr 23 '14 at 15:04
You're probably running Sql 2000 compatability - Ouch. If you have access to do so, back up your DB and head over to [MSDN](http://technet.microsoft.com/en-us/library/bb933794.aspx). Sql 2005 is compatability level 90. – StuartLC Apr 23 '14 at 15:09
I wont be able to do that on the work computer. extremely important database so a bit too risky. Looking at your sql fiddle this method is perfect... wish i could use it :( – Seanin Apr 23 '14 at 15:12
You might try and [make the case that Sql2005 is at the end of its lifespan](http://blogs.technet.com/b/cdnitmanagers/archive/2012/12/06/sql-server-2000-end-of-support-april-2013.aspx) and it may be time to upgrade? – StuartLC Apr 23 '14 at 15:22
work in a college.. we have talked about it but no funds available at present. But in our work these pivots could be so useful so I will have a word in someones ear. – Seanin Apr 23 '14 at 15:25

score 1 · Answer 2 · answered Apr 23 '14 at 14:39

1

Group by should solve this, Something like following:

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

answered Apr 23 '14 at 14:39

Saurabh

71,488
40
181
244

This pulls the correct data but not in the format that I need to display it. – Seanin Apr 23 '14 at 14:48

Ray Henry · Answer 3 · 2014-04-23T14:51:32.553

1

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 GROUP BY SCHOOL, STUDENT) GROUP BY SCHOOL, N;

edited Apr 23 '14 at 14:51

answered Apr 23 '14 at 14:47

Ray Henry

905
6
12

+1 : Although this doesn't pivot the data as the OP requested, it's normalised structure does mean that it *does* handle students with more than 11 GCSEs. The pivoting is better suited to the presentation layer of an application than the sql layer. – MatBailie Apr 23 '14 at 14:49

SQL Count. How can I count how many distinct values are in a table when an other two columns are matching?

3 Answers3

Linked