Add row with zero value when in MySQL when record is not found for a particular month

Question

It was hard to find a good title for my question. I have 3 tables: materials, orders and order_contents. There are 5 different types of materials in the materials table The orders table contains the dates for the orders. Orders currently span over 4 months. The orders are filled with materials in the table called order_contents.

I am trying to get the overall cost per month for materials and display them in a highchart. Here's the query I run:

SELECT m.name, CONCAT(MONTH(o.order_date), '/', YEAR(o.order_date)) as `month`, SUM(oc.weight * m.price) AS cost
    FROM order_contents oc 
    INNER JOIN orders o ON oc.order_id = o.id
    INNER JOIN materials m ON oc.material_id = m.id
    GROUP BY MONTH(o.order_date), m.id
    ORDER BY m.name, order_date ASC

Here are the results:

The problem is that if a material isn't used in a particular month, it won't generate a record for it (obviously). So when I loop through the results and try to form the hightable data series, it won't fill a month with zero. For example, the material Big Bag is only consumed in January 2022, but since it's the only entry in the data series, it maps with the first month, which is August. I can add logic to fix this problem but I thought I'd ask here first if there is a way to reformat this query to yield the results I'm looking for.
Here's what I'd like to get:

I'm way out of my league here on SQL capabilities for this sort of problem.

Answer 1

Here is a (probably wired) idea:

For a SQL table:

create table temp
(
    month int,
    year int,
    name varchar(16), -- something like material-type
    count int,
)

We could run:

select const_year.year, const_month.month, const_name.name, ifnull(count, 0)
from (select 1 month union
select 2 union
select 3 union
select 4 union
select 5 union
select 6 union
select 7 union
select 8 union
select 9 union
select 10 union
select 11 union
select 12) const_month -- now we have a list contains 12 months

left join (select 2020 year union
select 2021 union
select 2022 ) const_year
on true -- now we have a table contains all months between those years

left join (select distinct temp.name as name
from temp) const_name
on true -- join with all distinct names/types

left join (select temp.name as name, temp.year as year, temp.month as month, sum(count) as count
from temp
group by temp.year, temp.month, temp.name -- here is the real query for statistic
) statistic
on statistic.year = const_year.year
and statistic.month = const_month.month
and statistic.name = const_name.name

order by name, year, month -- order results if we need

I think there definitely have more better solution than this. Though it's working for some case.