Postgres - How to get Multi-Count Query using a Where Clause for a Join Table I have the following query that gets the counts across multiple tables that are joined:

select
    count(distinct e.planningitemdata-&gt;&gt;'receiverDuns') as supplierOrCustomer,
    count(distinct e.planningitemdata-&gt;&gt;'shipTo') as shipTo,
    count(distinct e.planningitemdata-&gt;&gt;'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-&gt;&gt;'receiverDuns') as supplierOrCustomer,
        count(distinct e.planningitemdata-&gt;&gt;'shipTo') as shipTo,
        count(distinct e.planningitemdata-&gt;&gt;'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 &gt;='2018-03-06' and o.deliveryrequesteddate &lt;= '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.
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 &gt;= '2018-03-06'
                         and o.deliveryrequesteddate &lt;= '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.