1

Possible Duplicate:
MySQL how to fill missing dates in range?

I have a table with the following columns:

  • id,
  • submitdate,
  • clicks.

Now I want to retrieve the number of clicks per day from 31 days.

If there is no records on particular date, it should be zero.

Community
  • 1
  • 1
jaya
  • 327
  • 3
  • 4
  • 16

1 Answers1

1

Try this:

SELECT 
  DATE(submitdate) 'Day',
  SUM(clicks)
FROM Table
WHERE DATEDIFF(NOW(), submitdate) <= 31
GROUP BY DATE(submitdate);

SQL Fiddle Demo

Update: For this you will need to generate a list of the last n days from the current date NOW(). I used for this an anchor table:

CREATE TABLE TEMP 
(Digit int);
INSERT INTO Temp VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

Then I used it to generate a list of the last n days from now(in the demo I used 31 days but you can try it with any value less than 100, if you want to get more than 100 you will need to CROSS JOIN temp table one more time) I used the following query:

    SELECT 
      DATE_SUB(NOW(), INTERVAL id DAY) GDay
    FROM
    (
      SELECT t2.digit * 10 + t1.digit + 1 AS id
      FROM         TEMP AS t1
      CROSS JOIN TEMP AS t2
    ) t WHERE id <= 31;

Then with LEFT JOIN you can get the last 31 days and if there are no records there will be 0 instead:

SELECT
  DATE(t2.GDay) 'Day', 
  SUM(IFNULL(t1.clicks, 0)) 'Count of clicks'
FROM
(
    SELECT 
      DATE_SUB(NOW(), INTERVAL id DAY) GDay
    FROM
    (
      SELECT t2.digit * 10 + t1.digit + 1 AS id
      FROM         TEMP AS t1
      CROSS JOIN TEMP AS t2
    ) t WHERE id <= 31
) t2 
LEFT JOIN Table1 t1 ON DATE(t1.submitdate) = DATE(t2.GDay)
GROUP BY DATE(t2.GDay)
ORDER BY DAY DESC;

SQL Fiddle Demo

Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
  • 2
    this is a great demo but still doesn't answer the OP, he wants to display 0 for date that have no records. from your example, you need to show records from November 1 to November 31. – Walter Nov 17 '12 at 15:03
  • 1
    @darasaquin - Sorry, I missed this point. I updated my answer. Please review it. Thanks. – Mahmoud Gamal Nov 17 '12 at 15:45
  • thanks for giving reply. its givig result for current date and takin too much of time – jaya Nov 18 '12 at 03:53
  • @jayamalladi - How this isn't working? What do you mean by its giving results for current date?. This should give you the list of 31 days from `now()`(current date), if you didn't need from the current date you can replace `NOW()` with any given date. – Mahmoud Gamal Nov 18 '12 at 07:45
  • ok i got your idea. but here what i am saying is i need to take count(id). because clicks is not a int datatype. – jaya Nov 18 '12 at 08:11