Postgres default aggregation value for empty row

Question

I have written a query that's doing an aggregation of X.

It starts from this week's Monday up to current day.

select 
  extract(dow from time_sent) days
  , count(id)
from messages
where time_sent between date_trunc('week', now()) and now()
group by days
order by days;

I would like a hint on how to set a default value for Monday when no row was added on Monday.

ie, Monday was empty, so I'm getting this:

[ anonymous { day: 2, count: '1' },
  anonymous { day: 3, count: '1' },
  anonymous { day: 4, count: '1' },
  anonymous { day: 5, count: '1' },
  anonymous { day: 6, count: '1' } ]

Expected result:

[ anonymous { day: 1, count: '0' },
  anonymous { day: 2, count: '1' },
  anonymous { day: 3, count: '1' },
  anonymous { day: 4, count: '1' },
  anonymous { day: 5, count: '1' },
  anonymous { day: 6, count: '1' } ]

Edit: Add table structure and sample output.

id | time_sent 
1  | 2018-01-13 15:26:21.443828+08
2  | 2018-01-12 15:26:21.44755+08
3  | 2018-01-11 15:26:21.459208+08
4  | 2018-01-10 15:26:21.440648+08
5  | 2018-01-09 15:26:21.457262+08

Query #1:

select 
  coalesce(extract(dow from time_sent), d) days
  , count(message_batch_id)
from generate_series(0,6) d
left join messages 
on d = extract(dow from time_sent)
where time_sent between date_trunc('week', now()) and now()
group by days
order by days;

Query #1 output:

days | count
2    | 1
3    | 1
4    | 1
5    | 1
6    | 1

Edit: I need to clarify that the messages table didn't have any row entry on Monday.

Edit:

For some reason, this query returns the row structure that I wanted:

select 
    extract(dow from d.*) days, 
    count(id) 
from GENERATE_SERIES(DATE_TRUNC('WEEK', NOW()), NOW(), '1 
DAY'::INTERVAL) d
left outer join messages m
on m.time_sent::DATE = d
group by days
order by days;

Re your edit: 1. Read a definition of left join. 2. If that's a solution, please post as an answer & accept after the minimal wait. If it's not a solution, why is it there? What does "row structure" mean? PS It's good to give all that info, but the format is not useful as runnable code. [mcve] — philipxy, Jan 19 '18 at 04:57
Possible duplicate of [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/questions/4707673/left-outer-join-doesnt-return-all-rows-from-my-left-table) — philipxy, Apr 28 '19 at 21:15
Learn what LEFT JOIN ON returns: INNER JOIN ON rows UNION ALL unmatched left table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. A WHERE or INNER JOIN ON that requires a right [sic] table column to be not NULL after an OUTER JOIN ON removes any rows extended by NULLs, ie leaves only INNER JOIN ON rows, ie "turns OUTER JOIN into INNER JOIN". You have that. — philipxy, Apr 28 '19 at 21:16

Vao Tsun · Answer 1 · 2018-01-13T18:07:48.873

0

try:

select 
  coalesce(extract(dow from time_sent),d) days
  , count(id)
from generate_series(0,6) d
left outer join messages on d = extract(dow from time_sent)
where time_sent between date_trunc('week', now()) and now()
group by days
order by days;

(did not test)

edited Jan 13 '18 at 18:07

answered Jan 13 '18 at 17:19

Vao Tsun

47,234
13
100
132

I'm getting the same result :( . The returned row doesn't include Monday(1). – deojeff Jan 13 '18 at 18:01
is it an empty row,or just no row? editted a little - please try again – Vao Tsun Jan 13 '18 at 18:08
Sorry. I'm kinda new with postgres. It's no row at all. – deojeff Jan 13 '18 at 18:09
I tried the updated query. I'm still getting the same result. – deojeff Jan 13 '18 at 18:12
well question and answer seem obvious. if it does not help, please update your post with structure and data sample. I don't see what could be wrong – Vao Tsun Jan 13 '18 at 18:19
left join and `left outer join` are really different :) `outer ` is meant to solve your problem, not `left` – Vao Tsun Jan 13 '18 at 18:41
I tried using `left outer join` but still no luck, it's still showing the same result. – deojeff Jan 14 '18 at 06:17

clemens · Answer 2 · 2018-01-13T18:28:01.683

0

The predicate can't extend the rows. You have to generate the dates with GENERATE_SERIES and join this with messages:

SELECT extract(dow from time_sent) days, coalesce(count(id), 0)
FROM GENERATE_SERIES(DATE_TRUNC('WEEK', NOW()), NOW(), '1 DAY'::INTERVAL) d 
    LEFT JOIN messages ON time_sent::DATE = d
GROUP BY days
ORDER BY days;

edited Jan 13 '18 at 18:28

answered Jan 13 '18 at 17:20

clemens

16,716
11
50
65

I'm getting the same result. Monday(1) is still not included. – deojeff Jan 13 '18 at 18:01
Sorry, you need a left join. – clemens Jan 13 '18 at 18:20
I'm getting the result for a dummy Monday, but the but instead of getting 1 as the days value, I'm getting NULL. – deojeff Jan 13 '18 at 18:25
1

`COALESCE` will help. ;) – clemens Jan 13 '18 at 18:28

score 0 · Answer 3 · edited Apr 28 '19 at 14:58

0

For some reason this return the result that I'm expecting. ie,

Returns the row of the day in a week (even when there's no row entry for that particular day)

select 
    extract(dow from d.*) days, 
    count(id) 
from GENERATE_SERIES(DATE_TRUNC('WEEK', NOW()), NOW(), '1 
DAY'::INTERVAL) d
left outer join messages m
on m.time_sent::DATE = d
group by days
order by days;

edited Apr 28 '19 at 14:58

Zoe

27,060
21
118
148

answered Jan 19 '18 at 05:00

deojeff

377
6
19

Postgres default aggregation value for empty row

3 Answers3