Mysql group by months, but show 0 for months w/ no data

Grafana MySQL
1

  • What Grafana version and what operating system are you using?
    9.5.2

  • What are you trying to achieve?
    Display coalesced data from two tables by month, but some months have no data, so I want to display a 0

This is the query I’ve tried

select tn.trans_date, SUM(COALESCE(stn.wit, tn.wit)) as Recreation
FROM fn_trans AS tn 
LEFT JOIN fn_sub_trans AS stn ON stn.L_trans_id = tn.trans_id 
WHERE (tn.L_cat_id IN (68,73) OR stn.L_cat_id in (68,73) ) AND tn.deleted_at IS NULL
group by YEAR(tn.trans_date), MONTH(tn.trans_date) 
ORDER BY tn.trans_date DESC LIMIT 24

This gives a result like

2023-05-02  90.0000
2023-04-17  70.2600
2023-03-27  10.0000
2023-01-08  18.1700

But you can see Feb is missing. I instead want it to show w/ a 0.

I tried making a table of years w/ months to use as a join, here is that query

FROM yearmonth as ym
LEFT JOIN fn_trans AS tn ON (MONTH(tn.trans_date) = ym.month AND YEAR(tn.trans_date) = ym.year)
LEFT JOIN fn_sub_trans AS stn ON stn.L_trans_id = tn.trans_id 
WHERE (tn.L_cat_id IN (68,73) OR stn.L_cat_id in (68,73) ) AND tn.deleted_at IS NULL
group by YEAR(tn.trans_date), MONTH(tn.trans_date) 
ORDER BY tn.trans_date DESC LIMIT 24

That did the same. BUT, then I read that using WHERE outside of the join can make the LEFT join not work correctly, and you should replace WHERE with AND, so it’s part of the join.

That almost works. I get the following, w/ Feb present.

2023-06-02  0.0000
2023-05-01  2813.9700
2023-04-06  17238.0400
2023-03-02  56157.4400
2023-02-01  6888.6900
2023-01-02  9867.5000

But now the math is all wrong. I can’t figure out how to get the Sum/coalesce to work correctly.

Is there a better mysql or, ideally, grafana way to just have the bar graph say, if a month is missing, just put a zero?

2

Maybe try the left join as a subquery so that the where doesn’t trip things up

Meaning get your data first then left join to missing months