Postgres - How to get Multi-Count Query using a Where Clause for a Join Table

Question

I have the following query that gets the counts across multiple tables that are joined:

select
    count(distinct e.planningitemdata->>'receiverDuns') as supplierOrCustomer,
    count(distinct e.planningitemdata->>'shipTo') as shipTo,
    count(distinct e.planningitemdata->>'productId') as product,
    count(distinct eo.orderlineid) as orderline,
    count(distinct e.planningitemid) as planningitem 
from
    eventplanningitem e 
join
    eventorderline eo 
        on e.eventid = eo.eventid
join
    orderline o 
        on eo.orderlineid = o.id
        and o.deliveryrequesteddate between '2018-03-06' AND '2018-05-06'
where
    e.eventId = '9f6d3d50-05ca-4441-a4e4-24de2b52de5b'

I want to filter the "orderlineid" counts based on a date filter, but my issue is that when I try to apply it to the join as shown above, it 0's out all the results of all the other counts. Same thing happens if I try to add a where clause to the end. If I make the orderline table a left outer join, I get the count of the orderlines that don't match the date criteria.

Result:

0;0;0;0;0

A similar version produces the same thing:

select
        count(distinct e.planningitemdata->>'receiverDuns') as supplierOrCustomer,
        count(distinct e.planningitemdata->>'shipTo') as shipTo,
        count(distinct e.planningitemdata->>'productId') as product,
        count(distinct eo.orderlineid) as orderline,
        count(distinct e.planningitemid) as planningitem 
    from
        eventplanningitem e 
    join
        eventorderline eo 
            on e.eventid = eo.eventid
    join
        orderline o 
            on eo.orderlineid = o.id
            and o.deliveryrequesteddate between '2018-03-06' AND '2018-05-06'
    where
        e.eventId = '9f6d3d50-05ca-4441-a4e4-24de2b52de5b' 
        and (o.deliveryrequesteddate >='2018-03-06' and o.deliveryrequesteddate <= '2018-05-06')

I get counts if I make the orderline join a left outer join, but it includes the orderLine records that actually fall outside the date criteria so it's also not correct.

I want to avoid breaking this into multiple queries but I wanted to check if I'm missing something before going that route.

Answer 1

An alternative @RDFozz's answer would be to use FILTER:

count(distinct eo.orderlineid) filter 
    (where o.deliveryrequesteddate between '2018-03-06' AND '2018-05-06') 
    as orderline,

I think it is bit more intuitive, as this is the exact reason for FILTER to exist so its intention is more obvious than using CASE.

Answer 2

It looks like you can use COUNT(DISTINCT expression), so I'd try replacing

count(distinct eo.orderlineid) as orderline,

with this

COUNT(DISTINCT CASE WHEN     o.deliveryrequesteddate >= '2018-03-06'
                         and o.deliveryrequesteddate <= '2018-05-06'
                 THEN eo.orderlineid
                 ELSE NULL
               END) as orderline,

NULL values are not counted by COUNT, and the DISTINCT should still ensure you're only counting each orderlineid once.