15

I have SQL like this (where $ytoday is 5 days ago):

$sql = 'SELECT Count(*), created_at FROM People WHERE created_at >= "'. $ytoday .'" AND GROUP BY DATE(created_at)';

I want this to return a value for every day, so it would return 5 results in this case (5 days ago until today).

But say Count(*) is 0 for yesterday, instead of returning a zero it doesn't return any data at all for that date.

How can I change that SQLite query so it also returns data that has a count of 0?

JBurace
  • 5,123
  • 17
  • 51
  • 76

2 Answers2

13

Without convoluted (in my opinion) queries, your output data-set won't include dates that don't exist in your input data-set. This means that you need a data-set with the 5 days to join on to.

The simple version would be to create a table with the 5 dates, and join on that. I typically create and keep (effectively caching) a calendar table with every date I could ever need. (Such as from 1900-01-01 to 2099-12-31.)

SELECT
  calendar.calendar_date,
  Count(People.created_at)
FROM
  Calendar
LEFT JOIN
  People
    ON Calendar.calendar_date = People.created_at
WHERE
  Calendar.calendar_date >= '2012-05-01'
GROUP BY
  Calendar.calendar_date
MatBailie
  • 83,401
  • 18
  • 103
  • 137
  • Hmm, seems simple enough. This is always the last 5 days though, so a static table wouldn't work. Thoughts? – JBurace May 14 '12 at 15:58
  • @JBurace - A static table does work. You just change your WHERE clause. For example, mine shows "every date since `'2012-05-01'`" because I'm lazy and didn't specify a 5 day range in that WHERE clause. Then you do as you already are and build that WHERE clause to suit your needs. – MatBailie May 14 '12 at 15:59
  • Hmm okay. I thought `create a table with the 5 dates` meant create a table with only 5 entries. – JBurace May 14 '12 at 16:01
  • @JBurace - `I typically create and keep (effectively caching) a calendar table with every date I could ever need. (Such as from 1900-01-01 to 2099-12-31.)` – MatBailie May 14 '12 at 16:06
5

You'll need to left join against a list of dates. You can either create a table with the dates you need in it, or you can take the dynamic approach I outlined here:

generate days from date range

Community
  • 1
  • 1
D'Arcy Rittich
  • 167,292
  • 40
  • 290
  • 283