Select count even when 0 postgres

Question

I want to count a bunch of entries past a date. However, I also want to have results that say 0. But the current query drops all 0 results as soon as i add the filter by date clause. Any ideas how to get those 0 entries?

select distinct (e.customer_id), count(m.id) as msgs from eng_msgs e
join messages m on e.customer_id = m.customer_id
where e.customer_id = m.customer_id
and m.created_at > e.created_at -- this line removes 0 results
group by e.customer_id

Clodoaldo Neto · Accepted Answer · 2017-08-03 22:44:34Z

1

You need an outer join condition:

select e.customer_id, count(m.id) as msgs
from
    eng_msgs e
    left outer join
    messages m on
        e.customer_id = m.customer_id
        and
        m.created_at > e.created_at
group by e.customer_id

answered Aug 3, 2017 at 22:44

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Select count even when 0 postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related