I have 2 tables, one containing Order information and one containing Order Event information, example structure below:

Orders Table:

Events Table:

Desired Output:

I am trying to get a breakdown by Merchant Id and month of:

• Order Count
• Order Sum
• Expiry Count (how many of the orders placed in that month have expired regardless of date expired)
• Expiry Sum (value of the expiry count above)

Example Output:

I have tried a few queries with no luck, the furthest I've gotten is:

select o.merchant_id, extract(month from o.order_date) as order_month, count(o.order_id) as order_count, sum(o.order_amount) as order_sum, count(e.order_id) as expiry_count, sum(e.amount) as expiry_sum
from orders o
left join events e on e.order_id = o.order_id
where o.merchant_id = '111111'
and o.order_date >= '2021-07-01'
group by o.merchant_id, order_month
order by o.merchant_id, order_month

However that outputs the exact same values for order_count & expiry_count, as well as order_sum & expiry_sum. Additionally I need to only retrieve events where event = 'EXPIRY' however I get no results when I add that filter.

Any help would be much appreciated!

CodePudding user response：

If I read your question correctly…

Add the condition on event to the join (not the where):

select o.merchant_id, extract(month from o.order_date) as order_month, count(o.order_id) as order_count, sum(o.order_amount) as order_sum, count(e.order_id) as expiry_count, sum(e.amount) as expiry_sum
from orders o
left join events e on e.order_id = o.order_id
    and e.event = 'EXPIRY'
where o.merchant_id = '111111'
and o.order_date >= '2021-07-01'
group by o.merchant_id, order_month
order by o.merchant_id, order_month

If you put a condition on an outer joined table to the where clause, you force the join to behave as an inner join (as if you deleted the left keyword).