MySQL - count distinct values from one column based on another column

Question

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,

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

Is there any reason you need that exact structure in MySQL and can't do it in your application layer? What's ideal from a representational relational structure is not always ideal in terms of display, so that's where application code comes into play. — tadman
– tadman, Commented Sep 19, 2017 at 6:59
Can you explain the desired result? from how the numbers 5, 8, 4, 6 should calculated?? — user8608587
– user8608587, Commented Sep 19, 2017 at 7:02
@DamienSimone The numbers 5,8,4,6 is the COUNT should be taken from the Grade Column in the main table.I gave 5, 8, 4, 6 Numbers for sample only. — user8631152
– user8631152, Commented Sep 19, 2017 at 7:07
@AnilKumarReddy Yes, but the counts are 3,1,1,2? why 5,8,4,6 and the other values? — user8608587
– user8608587, Commented Sep 19, 2017 at 7:08

user8608587 · Accepted Answer · 2017-09-19 07:10:19Z

3

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

answered Sep 19, 2017 at 7:10

user8608587

Sign up to request clarification or add additional context in comments.

1 Comment

M Khalid Junaid Over a year ago

A shorthand will be only SUM( Grade = 'E3')

Anil Kumar Reddy · Accepted Answer · 2017-09-19 07:32:21Z

0

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;

answered Sep 19, 2017 at 7:32

Anil Kumar Reddy

1021 gold badge1 silver badge16 bronze badges

Collectives™ on Stack Overflow

MySQL - count distinct values from one column based on another column

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related