Show all dates between, even if no result In group by

Question

SELECT Count(Phq_2) as Phq_2, Date(created) as created
FROM `survey` 
WHERE Phq_2 != '' 
GROUP BY Date(created) 
ORDER BY Date(created) ASC

Showing this

This result which is query giving

Expected Result

I want this result

Youu need to create a temp table [WITH] that produces all the date you are interested in and the LEFT JOIN with your query. I think is a dublicate question, I've seen it many times! — Ted at ORCL.Pro, Sep 19 '17 at 21:39
Some ideas at https://stackoverflow.com/questions/1046865/mysql-select-all-dates-in-a-range-even-if-no-records-present — EdmCoff, Sep 19 '17 at 21:42
Please help me out i tried to solve from existing question but in vain — Muteeb Ahmed Kamboh, Sep 19 '17 at 21:42
Consider handling issues of data display in application code — Strawberry, Sep 19 '17 at 22:48

score 2 · Answer 1 · answered Sep 19 '17 at 22:48

you can create a list of dates where you can use to list and join on your table. table c on the query below is the date table.

SELECT c.date, 
       COUNT(s.phq_2) 
  FROM (select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) date from
        (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
        (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
        (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
        (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
        (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4
       ) c
  LEFT JOIN `survey` s 
    ON c.date = s.created
 WHERE s.phq_2 != '' 
   AND c.date BETWEEN '2017-08-30' AND '2017-09-06'
 GROUP BY c.date
 ORDER BY c.date

Result

date        COUNT(s.phq_2)
2017-08-30  1
2017-08-31  1
2017-09-01  0
2017-09-02  0
2017-09-03  0
2017-09-04  0
2017-09-05  1
2017-09-06  6

if you want the dates fixed for WHERE c.date BETWEEN '2017-08-30' AND '2017-09-06', you can use the resulting MIN() and MAX() of column created from your table in a sub-query

cool, so just use this one and add left join to our table, fix my trouble , thanks. — Anthony Kal, Mar 03 '18 at 07:51

Show all dates between, even if no result In group by

1 Answers1