SQL查询以获取字段值分布 [英] SQL query to get field value distribution

问题描述

我有一张超过一百万个测试分数记录的表，这些记录基本上具有唯一的score_ID，subject_ID和一个人给出的分数.大多数受试者的分数范围是0-3，但有些受试者的分数范围是0-4.大约有25个可能的主题.

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
.
.

因此它按subject_ID对数据进行分组，然后显示在该主题内给出特定分数值的次数.

So it groups the data by subject_ID, then shows how many times a specific score value was given within that subject.

任何产生此信息的SQL指针将不胜感激.

Any SQL pointers to generate this would be greatly appreciated.

推荐答案

Select subject_id
    , Sum( Case When Score = 0 Then 1 Else 0 End ) As [0]
    , Sum( Case When Score = 1 Then 1 Else 0 End ) As [1]
    , Sum( Case When Score = 2 Then 1 Else 0 End ) As [2]
    , Sum( Case When Score = 3 Then 1 Else 0 End ) As [3]
    , Sum( Case When Score = 4 Then 1 Else 0 End ) As [4]
From Table
Group By subject_id

这篇关于SQL查询以获取字段值分布的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！