I'm trying to aggregate data by month from two tables. Often there is no record created for some months, I would like those months to be 0. This is the basic query:
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 my a result like
2023-05-02 90.0000
2023-04-17 70.2600
2023-03-27 10.0000
2023-01-08 18.1700
But I want February there, but 0 instead of just missing.
I tried making a big table of years w/ months to act as an initial table. Here is that query:
select tn.trans_date, SUM(COALESCE(stn.wit, tn.wit)) as Recreation, ym.year, ym.month
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
But this doesn't work either, still no Feb. I read around and I it seems it's because the WHERE clause makes my LEFT join not work like a typical LEFT join.
So somewhere suggested making it AND instead of WHERE. Which I tried, and then I DO get February, but the math is all wrong.
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
How do I get 0 values for missing (not null) records in a month.
I've tried looking at this, but I'm trying to import this into grafana and can't use procedures or multiple queries.
I've looked at this which is where I learned about AND vs WHERE issue, which almost works, but then the math for the SUM/COALESCE doesn't work.
