[SOLVED] Get count of created entries for each day

Get count of created entries for each day

Let's say I have a this search query like this:

SELECT COUNT(id), date(created_at)
FROM entries
WHERE date(created_at) >= date(current_date - interval '1 week')
GROUP BY date(created_at)

As you know then for example I get a result back like this:

count | date
  2   |  15.01.2014
  1   |  13.01.2014
  9   |  09.01.2014

But I do not get the days of the week where no entries where created.

How can I get a search result that looks like this, including the days where no entries where created?

count | date
  2   |  15.01.2014
  0   |  14.01.2014
  1   |  13.01.2014
  0   |  12.01.2014
  0   |  11.01.2014
  0   |  10.01.2014
  9   |  09.01.2014

Solution

SELECT day, COALESCE(ct, 0) AS ct
FROM  (SELECT now()::date - d AS day FROM generate_series (0, 6) d) d  -- 6, not 7
LEFT   JOIN (
   SELECT created_at::date AS day, count(*) AS ct 
   FROM   entries
   WHERE  created_at >= date_trunc('day', now()) - interval '6d'
   GROUP  BY 1
   ) e USING (day);

Use a sargable expression for your WHERE condition, so Postgres can use a plain index on created_at. Far more important for performance than all the rest.

To cover a week (including today), subtract 6 days from the start of "today", not 7. Alternatively, shift the week by 1 to end "yesterday", as "today" is obviously incomplete, yet.

Assuming that id is defined NOT NULL, count(*) is identical to count(id), but slightly faster. See:

Why is count(x.) slower than count()?

A CTE is not needed for the simple case. Would be slower and more verbose.

Aggregate first, join later. That's faster.

now() is Postgres' short syntax for the standard SQL CURRENT_TIMESTAMP (which you can use as well). See:

Difference between now() and current_timestamp

This should be the shortest and fastest query. Test with EXPLAIN ANALYZE.