Count zero values from DB
How can I return dataset in chart when I use query to count zero values by month and year? So it shows null value on graph if there is no data on that specific date
2 Replies
What does your query look like right now? I assume you are grouping by year and month? And your problem is, this only provides data for years / months if there are rows with those dates in your source table, so you end up with gaps in your month totals, where entire months are skipped.
This is a well known problem in database grouping by months. The usual way round it is to create a "months" table with one row and a date field for each month of the year, then outer join that to your query. Or do an right join on a long "UNION SELECT 1 as month UNION SELECT 2 as month ..." (see attached Stack Overflow answer).
So show your existing query, and we'll have a go at it.
https://stackoverflow.com/questions/42543198/mysql-group-by-month-with-null-values-if-0-entities
Stack Overflow
Mysql group by month with null values if 0 entities
Really simple (can't find an example on here). Basically I want to add in the null values for missing months.
Currently I have
|month| total |
----------------
2 | 2454.34
3 | 1254.34
...
I already solved it thanks 🙂