Get the number of created entries for each day

Question

Let's say I have a search 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, for example, I return the result as follows:

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

But I do not get days of the week where there are no entries that were created.

How can I get a search result that looks like this: including days when there are no entries that were 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 

Answer 1

 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 the sargable expression for your WHERE so Postgres can use the regular index for created_at . Much more important for performance than everyone else.

• To cover the week (including today), subtract 6 days from the start of today, not 7.

• Assuming id defined as NOT NULL , count(*) is identical to count(id) , but a little faster.

• CTE would be redundant here. It is slower and more verbose.

• Aggregate first, join later. It is faster in this case.

• now() is a slightly shorter and faster implementation of Postgres standard SQL CURRENT_TIMESTAMP (which you can also use).

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

Connected with:

• Select amount and current balance for the last 18 months using generate_series
• PostgreSQL: counting the number of rows for a query by minute

Answer 2

Try this query:

 with a as (select current_date - n as dt from generate_series(0, 6) as t(n)), b as (select count(id) cnt, date(created_at) created_at from entries where date(created_at) >= date(current_date - interval '1 week') group by date(created_at)) select coalesce(b.cnt,0), a.dt from a left join b on (a.dt = b.created_at) order by a.dt; 

count function will not generate 0 for nonexistent lines. So you need to fill in the lines for missing dates. Using generate_series and simple date arithmetic, you can create strings for dates of a certain period (in this case, 1 week). Then you can use the outer join to get the final result. coalesce will replace null with 0 .

Answer 3

You need to say SQL to handle NULL. Return 0 if NULL

You can do it with COALESCE

http://www.postgresql.org/docs/devel/static/functions-conditional.html

Answer 4

Use generate_series() to create the desired dates and JOINs in this date list:

 SELECT COUNT(id), date(gdate) FROM entries RIGHT JOIN generate_series(current_date - interval '1 week', current_date, '1 day') g(gdate) ON date(created_at) = date(gdate) AND date(created_at) >= date(current_date - interval '1 week') GROUP BY date(gdate) ORDER BY date(gdate) DESC;