CODEWITHSUNDEEP
phpmysql
Andy
Andy

Reputation: 138

Query to display results next to each day of month

I have a calendar table setup, I have left joined another table based on matching days but wish to display the rest of the full month even if no matches, I am trying to build a graph of current month showing each day's progress.

My query is: 

SELECT calTable.db_date, COUNT(DISTINCT tbl_data.id) as resultsCounts
FROM calendarTable as calTable
LEFT JOIN tbl_data as tbdata ON DATE(tbl_data.startDate) = DATE(calTable.db_date) 
WHERE calTable.db_date between '2019-04-01' AND '2019-04-30' 
GROUP BY calTable.db_date

I realise as soon as I join the other table which has no future dates that this is causing the rest of the dates not to show, what can I do to show the rest of the current month,

array (size=3)
'db_date' => string '2019-04-07' (length=10)
'resultsCounts' => string '19' (length=2)

array (size=3)
'db_date' => string '2019-04-08' (length=10)
'resultsCounts' => string '0' (length=2)

array (size=3)
'db_date' => string '2019-04-09' (length=10)
'resultsCounts' => string '0' (length=2)

Same goes if I want to show last months data and if there is a day with no results, for it still to be included as 0 etc.

Calendar Table looks like this:

id | db_date | year | month | day | quarter | week | day_name | month_name 

20190407 | 2019-04-07 | 2019 | 4 | 7 | 2 | 14 | sunday | April

Apologies on formatting, not sure how to do do it.

Upvotes: 1

Views: 129

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133370

If you have valida date in calendarTable for all the date in range could your issue i related to the null result try adding an ifnull 

SELECT calTable.db_date, ifnull(COUNT(DISTINCT tbdata.id), 0) as resultsCounts
FROM calendarTable as calTable
LEFT JOIN tbl_data as tbdata ON DATE(tbdata.startDate) = DATE(calTable.db_date) 
WHERE calTable.db_date between str_to_date('2019-04-01', '%Y-%m-%d') 
      AND tr_to_date('2019-04-30' , '%Y-%m-%d')
GROUP BY calTable.db_date

Upvotes: 1

Related Questions