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(cr

Solution 1:

SELECTday, COALESCE(ct, 0) AS ct
FROM  (SELECT now()::date- d ASdayFROM generate_series (0, 6) d) d  -- 6, not 7LEFTJOIN (
   SELECT created_at::dateASday, count(*) AS ct 
   FROM   entries
   WHERE  created_at >= date_trunc('day', now()) -interval'6d'GROUPBY1
   ) 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.

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

• A CTE would be overkill here. It's slower and more verbose.

• Aggregate first, join later. That's faster in this case.

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

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

Related:

• Selecting sum and running balance for last 18 months with generate_series
• PostgreSQL: running count of rows for a query 'by minute'

Solution 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
           wheredate(created_at) >= date(current_date - interval '1 week')groupbydate(created_at))
select coalesce(b.cnt,0), a.dt
from a
left join b on (a.dt = b.created_at)
orderby a.dt;

count function will not generate 0 for non-existing rows. So you have to fill the rows for missing dates. With generate_series and simple date arithmetic, you can generate rows for dates of some period (in this case, 1 week). Then you can outer join to generate the final result. coalesce will substitute null to 0.

Solution 3:

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

You can do this by COALESCE

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

Solution 4:

Use generate_series() to create the dates you need and JOIN to this list of dates:

SELECTCOUNT(id), 
    date(gdate)
FROM entries
    RIGHTJOIN generate_series(current_date-interval'1 week', current_date, '1 day') g(gdate) 
    ONdate(created_at) =date(gdate) ANDdate(created_at) >=date(current_date-interval'1 week')
GROUPBYdate(gdate)
ORDERBYdate(gdate) DESC;