SQL计数。当其他两列匹配时,如何计算表中有多少个不同的值? [英] SQL Count. How can I count how many distinct values are in a table when an other two columns are matching?
问题描述
我试图完成一个sql查询以显示学生已记录的GCSE数量。]
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
例如,这个学生应该接受4的计数,因为有4个不同的科目分配到学校和学生ID。
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.
推荐答案
在学校和学生分组数据后,您需要通过 PIVOT
对于每个学科的学生数量,得到直方图'bins':
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;
我假设有限数量的主题,但是你可以使用 dynamic sql
I've assumed a finite number of subjects, but you can derive the columns as well using dynamic sql
这篇关于SQL计数。当其他两列匹配时,如何计算表中有多少个不同的值?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!