1

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.

2

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.

  • This is another great solution! Even cleaner than the case statement! – daniel9x Apr 9 '18 at 13:23
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.

  • This is exactly what I was looking for and solves the issue perfectly. Thank you, sir! – daniel9x Apr 7 '18 at 18:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.