Performing a Union on groups of rows without the same columns. Trying to generate empty histogram bins

I am using MySQL.

I have a table grades, that looks like so:

course_id grade
101        90
101        100
101        100
102        92
102        85
102        90

I have a view that counts how many grades are in each bin, for each class using the following query.

SELECT
    *,
    COUNT(grade_bin) as count
FROM
    (SELECT
        course_id, 
        FLOOR(grade/5.00)*5 AS grade_bin
    FROM ranked_grades) as t
        GROUP BY course_id, grade_bin
        ORDER BY course_id, grade_bin ASC;

This returns:

course_id grade_bin count
101         90        1
101         100       2
102         85        1
102         90        2

I want to add empty bins to this view, like so:

course_id grade_bin count
101          40       0
101          45       0
...         ...      ...
101          100      2
102          40       0
...         ...      ...

So the bins increment by 5, starting at 40, and stopping at 100. My thought was to make a table for empty bins, insert each grade bin and a count of 0, then use UNION but I can’t get the UNION to work, since I don’t have the course_id column in the empty bin table (nor do I want to – there are a lot and they could change). How would I do a UNION for each group of course_ids?

Answer

Assuming that you have created the table bin_table with only 1 column grade_bin and values 40, 45, 50, …., 100 and also that you have a table cources with the column course_id as primary key, all you have to do is CROSS join these tables and LEFT join to a query that aggregates on the table ranked_grades (no subquery needed):

SELECT c.course_id, b.grade_bin, COALESCE(t.count, 0) AS count
FROM bin_table b CROSS JOIN cources c
LEFT JOIN (
  SELECT course_id, FLOOR(grade / 5.00) * 5 AS grade_bin, COUNT(*) AS count
  FROM ranked_grades
  GROUP BY course_id, grade_bin
) t ON (t.course_id, t.grade_bin) = (c.course_id, b.grade_bin)