MariaDB/MySQL Statement To Show All Months Even When No Data Is Present

Wappler General How To
,
#1

Hi All,

I have a head bleeder and need some guidance from an SQL brainiac…!

I have a chart, works fine, but only shows the months that have data, looks like this (I know the ordering is off, this has been sorted):

image
image1339×528 6.13 KB

The data is coming from a table join with a ‘months’ table (id name):
image

and an archive table that lists our data (MariaDB/MySQL) from the ‘DateUpdated’ field.

So… I’ve joined the months table to the archive table and this works but it really should show all months, even if they have zero data. I’ve tried every iteration of the SQL and cant get the ‘empty’ months to show on that chart - can someone take a look for me please?

Heres my working SQL statement:

SELECT months.id AS X, COUNT(*) AS Y
FROM months
LEFT JOIN thearchive ON months.id = MONTH(DateUpdated)
WHERE YEAR(DateUpdated) = '2023'
    AND CID = '12233'
GROUP BY months.id;
#2

Try this:

SELECT months.id AS X, COUNT(thearchive.DateUpdated) AS Y
FROM months
LEFT JOIN thearchive ON months.id = MONTH(thearchive.DateUpdated) 
                     AND YEAR(thearchive.DateUpdated) = '2023'
                     AND thearchive.CID = '12233'
GROUP BY months.id;
#3

If the above doesn’t solve, can you show the JSON resultant of the database query?

I remember I experienced a similar issue, and ended up writing a formatter to fix the JSON to have zeroes on missing data (or something like that)

1 Like
#4

@kfawcett That’s it…!!! Many many (MANY) thanks for that! Im not sure if I’d have gotten to that solution on my own. Great work :slight_smile:

@Apple I was starting to think the ‘problem’ may have been something like this but @kfawcett found out it wasnt :slight_smile: thanks for your help though, appreciate your input.

image
image1288×483 7.2 KB

1 Like
#5

That is the case, when all the points to be plotted on X axis are not part of the result set, or have null as value.
Have used RunJS in multiple such occasions.
In this case, since count would return 0 instead of null, the graph renders well.

1 Like
#6

Just one more quick question if I may @kfawcett @Apple , what about trying a ‘between date range’ query using the above but using something like this so we could show the year on the chart starting at July 2023 and ending in the January 2024 :

AND thearchive.DateUpdated BETWEEN '2023-07-01' AND '2024-01-31'
The above only shows the chart starting at January

#7

You’ll probably need to replace dates and nums with parameters, but hopefully this gives you a working example.

SELECT months.id AS X, 
       IFNULL(SUM(CASE WHEN thearchive.DateUpdated BETWEEN '2023-07-01' AND '2024-01-31' THEN 1 ELSE 0 END), 0) AS Y
FROM months
LEFT JOIN thearchive ON months.id = MONTH(thearchive.DateUpdated) 
                     AND YEAR(thearchive.DateUpdated) IN (2023, 2024)
                     AND thearchive.CID = '12233'
WHERE months.id >= 7 OR months.id <= 1
GROUP BY months.id
ORDER BY CASE WHEN months.id >= 7 THEN 0 ELSE 1 END, months.id;
1 Like
#8

You can try ordering by YEAR(thearchive.DateUpdated) and months.id. Not sure if it will work with GROUP BY though.

1 Like
#9

That works perfectly! I’ll start adding the parameters and get it honed a little.

Appreciate the assistance, thank you :slight_smile:

1 Like