Get count of created entries for each day

Question

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

Erwin Brandstetter · Accepted Answer · 2022-03-27T01:43:43.280

6

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.

answered Mar 31 '15 at 07:57

Erwin Brandstetter

605,456
145
1,078
1,228

Thanks for your answer! If it works I will mark it as correct but currently I get an error: FEHLER: Unteranfrage in FROM muss Aliasnamen erhalten ZEILE 2: FROM (SELECT now::date - d AS day FROM generate_series (0, ... – John Smith Mar 31 '15 at 08:26
I fixed it by using `current date` and giving a alias for the table! Thanks – John Smith Mar 31 '15 at 09:17
@Erwin Brandstetter, how is `count` with `*` faster than `count` on one column ? Doesn't the width of row enlarge when choosing `*` over one column in aggregate function? – Kamil Gosciminski Mar 31 '15 at 10:06
@JohnSmith: Yes, the table alias was missing. – Erwin Brandstetter Mar 31 '15 at 10:20
1

@ConsiderMe: When using `*` in `count(*)` it is not actually decomposed into whole rows, it just stands for the row itself: the mere existence of a row is enough to count, the actual value does not have to be inspected, That's a bit faster. – Erwin Brandstetter Mar 31 '15 at 10:24
@ErwinBrandstetter thanks, good to know. Some (in the past) did argue with me about the width of columns that need to be counted, thus leading to better performance using one column as a feed for aggregate function like `COUNT()`. – Kamil Gosciminski Mar 31 '15 at 10:26
@ConsiderMe: Just run tests on any big table with `EXPLAIN ANALYZE`. The difference is small. – Erwin Brandstetter Mar 31 '15 at 10:30
1

@ErwinBrandstetter you're right. Tested with `*` and a `PK column`. There's a slight difference in favour of `*`. One more time, thank you. – Kamil Gosciminski Mar 31 '15 at 10:40
@ErwinBrandstetter MAybe you can also help me with this one? http://stackoverflow.com/questions/29371305/get-created-as-well-as-deleted-entries-of-last-week – John Smith Mar 31 '15 at 14:38

score 2 · Answer 2 · answered Mar 31 '15 at 07:15

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 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.

You can drop COALESCE(), COUNT(column) doesn't count NULL values. COUNT(*) counts records and doesn't check for NULL. — Frank Heikens, Mar 31 '15 at 07:33

score 0 · Answer 3 · answered Mar 31 '15 at 07:00

0

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

answered Mar 31 '15 at 07:00

sqluser

5,502
7
36
50

Thanks! But I dont understand It completly could you add a query please, specific to my question? – John Smith Mar 31 '15 at 07:05
A date that isn't there, won't pop up using COALESCE. This just doesn't work. – Frank Heikens Mar 31 '15 at 07:18

Frank Heikens · Answer 4 · 2015-03-31T07:53:04.840

-1

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

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;

edited Mar 31 '15 at 07:53

answered Mar 31 '15 at 07:18

Frank Heikens

117,544
24
142
135

@JohnSmith: What kind of errors/problems do you get? It must be something like this, but I didn't test it. – Frank Heikens Mar 31 '15 at 07:30
I get the same result as before! – John Smith Mar 31 '15 at 07:36
1

The WHERE condition has to move to the JOIN condition. I replaced "current_date" with a fixed date, my laptop is already using March.... – Frank Heikens Mar 31 '15 at 07:40
Oh this one is excellent! I would rate it as correct answer if you correct your query to use current_date! Thank you! – John Smith Mar 31 '15 at 07:52

Get count of created entries for each day

4 Answers4

Linked