Need to report latest status of a customer order, as well as the count of each order status for each customer

Question

Database Layout:

The question basically summarizes what I need to return.

There are multiple orders for each customer, but I need to report the status of the latest one.

I also need to make three columns of the count of the order status for each customers' orders ('Cancelled' 'Disputed' and 'On Hold').

If anyone could point me in the right direction that'd be super helpful.

Answer 1

You can use window functions and conditional aggregation:

select customernumber,
    max(case when rn = 1 then status end) as last_order_status,
    sum(status = 'Cancelled') cnt_cancelled,
    sum(status = 'Disputed' ) cnt_disputed,
    sum(status = 'On Hold'  ) cnt_on_hold
from (
    select o.*, 
        row_number() over(partition by customernumber order by orderdate desc) rn
    from orders o
) t
group by customernumber

Note that row_number() is available in MySQL 8.0 only. In earlier versions, you can use a correlatead subquer to retrieve the latest status:

select customernumber,
    (select o1.status from orders o1 where o1.customernumber = o.customernumber order by o1.orderdate desc limit 1) as last_order_status,
    sum(status = 'Cancelled') cnt_cancelled,
    sum(status = 'Disputed' ) cnt_disputed,
    sum(status = 'On Hold'  ) cnt_on_hold
from orders o
group by customernumber