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;

SqlFiddle这里

我假设有限数量的主题，但是你可以使用 dynamic sql

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

这篇关于SQL计数。当其他两列匹配时，如何计算表中有多少个不同的值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！