Range between date group by day get non existing row

Databases
1

Hi,


SELECT `video_view`.`video_id`, DATE_FORMAT(`date`,'%d/%m/%Y') AS `d`, COUNT(*) AS `tot_views` 
FROM `video_view`
WHERE (video_id = 169) AND (`date` > '2010-07-30 12:43:39') 
AND (`date` < '2010-08-06 12:43:39') 
GROUP BY DAY(`date`) ORDER BY `d` DESC

is there a way to get also the non existing row (no view in that day) ?

Bye

2

:stuck_out_tongue:

3 
SELECT '2010-07-30' + INTERVAL n DAY AS thedate
     , COUNT(video_view.video_id) AS tot_views 
  FROM numbers
LEFT OUTER
  JOIN video_view
    ON video_view.date >= '2010-07-30' + INTERVAL n DAY
   AND video_view.date  < '2010-07-30' + INTERVAL n+1 DAY
 WHERE '2010-07-30' + INTERVAL n DAY <= '2010-08-06'
GROUP 
    BY thedate
4

I do the query to retrieve the hits
for a week or for a month
so for instance all the days between
2010-07-30 12:43:39
and
2010-08-06 12:43:39

Thanks for your time

5

sure, just use a table of dates as the left table in a LEFT OUTER JOIN to your data

:slight_smile:

6

create the following table –

CREATE TABLE numbers ( n INTEGER NOT NULL PRIMARY KEY )

and then populate it with the numbers from 0 through 99

tell me which dates you want to generate and i’ll show you how to use this table

7

:slight_smile:
Well so I should do a think like


SELECT DATE(r.date),count(d.date) 
FROM dates AS r 
LEFT JOIN table AS d ON d.date = r.date 
GROUP BY DATE(r.date) 
ORDER BY r.date ASC;

http://stackoverflow.com/questions/75752/what-is-the-most-straightforward-way-to-pad-empty-dates-in-sql-results-on-either

but how to create the table dates ?