SQL和计数 [英] SQL and Counting

问题描述

我有一个包含以下内容的表

I have a table which consists of the following

Student Name        Grade       Class
--------------------------------------------------------
User 1              A           English
User 1              B           Math
User 2              B           Math

我正在尝试创建一个查询，其中将列出所有学生，及格的总成绩(A，B和C级)，A的总成绩，B的总成绩，C的总成绩

I'm trying to create a query where it will list all students, total pass grades (grades A,B & C), total A's, Total B's, Total C's

有人可以帮助我创建查询吗?或者至少可以使我朝正确的方向发展?

Can someone help me create the query or atleast put me in the right direction?

推荐答案

尝试一下:

select name,
    count(case when grade in ('A', 'B', 'C') then 1 end) totalPass,
    count(case when grade = 'A' then 1 end) totalA,
    count(case when grade = 'B' then 1 end) totalB,
    count(case when grade = 'C' then 1 end) totalC
from t
group by name

这是小提琴.

或者，如果您使用的是MySQL，我们可以使其更简单:

Or we can make it even simpler if you were using MySQL:

select name,
    sum(grade in ('A', 'B', 'C')) totalPass,
    sum(grade = 'A') totalA,
    sum(grade = 'B') totalB,
    sum(grade = 'C') totalC
from t
group by name

这是小提琴.

这篇关于SQL和计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！