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.

• daniel9x: This is another great solution! Even cleaner than the case statement!

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.

• daniel9x: This is exactly what I was looking for and solves the issue perfectly. Thank you, sir!