Grouping by different grade and know how much grade they are

Question

I have this kind of table :

Problem    UserID  Grade
Probleme1  ID1     33
Probleme1  ID2     100
Probleme1  ID4     57
Probleme1  ID6     57
Probleme1  ID78    24
Probleme1  ID5     24
Probleme2  ID1     37
Probleme2  ID12    88
Probleme2  ID6846  100
Probleme2  ID2     2

What I am trying to achieve, it's to create a table that will contain :

For each problem, the whole range of grades (from 0 to 100, even if no one had a 87 for this problem, I want it to be displayed with a 0 value)
For each grade (for each problem), the number of people that have obtained this grade.

So I have try to do smth like this :

DROP TABLE IF EXISTS Problem_Grades_Point;
CREATE TABLE Problem_Grades_Point (
  Platform VARCHAR(20),
  AnalyticsDomain VARCHAR(255),
  ShortTitle VARCHAR(255),
  Problem VARCHAR(255),
  Grade0 INT,
  Grade10 INT,
  Grade20 INT,
  INDEX (Problem)
) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO Problem_Grades_Point
SELECT
  Platform,
  AnalyticsDomain,
  ShortTitle,
  Problem,
  COUNT(Problem) AS Grade0,
  0 as Grade10,
  0 as Grade20
FROM First_Attempt_Problem_Grades
WHERE First_Grade = 0
GROUP BY Problem

UNION ALL 

SELECT
  Platform,
  AnalyticsDomain,
  ShortTitle,
  Problem,
  0 as Grade0,
  COUNT(t.Problem) AS Grade10,
  0 as Grade20
  FROM First_Attempt_Problem_Grades t
WHERE First_Grade = 10
GROUP BY Problem

UNION ALL 

SELECT
  Platform,
  AnalyticsDomain,
  ShortTitle,
  Problem,
  0 as Grade0,
  0 as Grade10,
  COUNT(Problem) AS Grade20
  FROM First_Attempt_Problem_Grades
WHERE First_Grade = 20
GROUP BY Problem

Of course, it doesn't contain everything I want and of course, it doesnt work. It displays only the result for the grade 0. All other grades are empty as follows:

The Mysql's query result

How can I do this SOV Community ? :)

Do I absolutely need to create a procedure ? – Joff Dec 12 '17 at 17:16 — Joff, Dec 12 '17 at 17:16
what mean `SOV`? – Juan Carlos Oropeza Dec 12 '17 at 18:25 — Juan Carlos Oropeza, Dec 12 '17 at 18:25
Mmf, my bad, I wanted to say SOF (Stack Over Flow) – Joff Dec 13 '17 at 09:27 — Joff, Dec 13 '17 at 09:27

score 0 · Accepted Answer · answered Dec 12 '17 at 18:34

First you need a list of grades. So you create a numbers table with grades from 1 to 100 and call it Grades

How do I get a list of numbers in MySQL?

Now you do:

SELECT P.Problem,
       G.Grade,
       COUNT(Y.UserID)
FROM ( SELECT DISTINCT Problem
       FROM YourTable) as P  -- if you have a table `Problems` you can use it instead
CROSS JOIN Grades as G
LEFT JOIN YourTable Y
  ON P.Problem = Y.Problem
 AND G.Grade = Y.Grade
GROUP BY P.Problem, G.Grade

Grouping by different grade and know how much grade they are

1 Answers1