how i can get last 10 days records with mysql?

mysql, sql

Solution

This is what you need:

SELECT days.day, count(survey.id)
FROM
  (select curdate() as day
   union select curdate() - interval 1 day
   union select curdate() - interval 2 day
   union select curdate() - interval 3 day
   union select curdate() - interval 4 day
   union select curdate() - interval 5 day
   union select curdate() - interval 6 day
   union select curdate() - interval 7 day
   union select curdate() - interval 8 day
   union select curdate() - interval 9 day) days
  left join survey
   on days.day = survey.submitdate
group by
  days.day

(if submitdate contanis date and time, you should replace the line `on days.day = survey.submitdate` with `on days.day = DATE(survey.submitdate)`)

Problem

my table structure was like ``` CREATE TABLE `survey` ( `id` int(11) NOT NULL auto_increment, `submitdate` datetime default NULL, `answer` varchar(5) collate utf8_unicode_ci default NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=499 ; ``` Now I want to calculate last 10 days records. Suppose no record on particular day. it should be zero. the output is like ``` date count 19-11-2012 10 18-11-2012 30 13-11-2012 0 . . ``` i have used query like ``` SELECT COUNT( * ) , DATE( submitdate ) FROM survey t WHERE t.submitdate >= ( CURDATE( ) - INTERVAL 10 DAY ) GROUP BY DATE( submitdate ) LIMIT 0 , 30 ``` output is ``` count(*) date(submitdate) 1 2012-11-13 2 2012-11-14 1 2012-11-15 3 2012-11-16 6 2012-11-17 ``` not giving 0 for no records with dates.

Original source