MySQL - 根据另一列计算一列中的不同值

i have the table resources which contains all data, i want to get the result as given in last table.. Please help me

我有包含所有数据的表资源，我想得到上表中给出的结果..请帮帮我

Dept Grade
A    E3  
B    M2  
D    E3  
C    M1  
A    E3  
D    E4  
A    E3

i used the below code but i am not able to get the actual solution

我使用下面的代码，但我无法得到实际的解决方案

SELECT DISTINCT Department,COUNT(DISTINCT GRADE) as Grades FROM reslookup GROUP BY Department;

Executed Result for the about query,

about查询的执行结果，

Dept Grade
A    3
B    1
C    1
D    2

But, i want to get the result like given below

但是，我希望得到如下所示的结果

Dept E3  E4  M1  M2
A    5   0   2   1
B    4   2   3   0
C    8   9   2   1
D    5   0   2   6

2 个解决方案

#1

You can also use the below code which gives the same solution

您也可以使用下面的代码，它提供相同的解决方案

SELECT Dept,
      SUM(Grade = 'E3') AS E3, 
      SUM(Grade = 'E4') AS E4,
      SUM(Grade = 'M1') AS M1,
      SUM(Grade = 'M2') AS M2
    FROM reslookup 
    GROUP BY Dept;

#2

What you are trying to do is pivot rows of the counts into different columns, and this can be done as following:

您要做的是将计数行转移到不同的列中，这可以通过以下方式完成：

SELECT Dept,
  SUM(CASE WHEN Grade = 'E3' THEN 1 ELSE 0 END) AS E3, 
  SUM(CASE WHEN Grade = 'E4' THEN 1 ELSE 0 END) AS E4,
  SUM(CASE WHEN Grade = 'M1' THEN 1 ELSE 0 END) AS M1,
  SUM(CASE WHEN Grade = 'M2' THEN 1 ELSE 0 END) AS M2
FROM reslookup 
GROUP BY Dept;

demo

演示

#1