Only display values of latest available event type

SQL Fiddle:

CREATE TABLE Purchasing (
    Event_Type VARCHAR(255),
    Campaign VARCHAR(255),
    Quantity_Offer VARCHAR(255),
    Quantity_Order VARCHAR(255),
    Quantity_Received VARCHAR(255)
);

INSERT INTO Purchasing
(Event_Type, Campaign, Quantity_Offer, Quantity_Order, Quantity_Received)
VALUES 
("Offer", "C001", "300", NULL, NULL),
("Offer", "C002", "200", NULL, NULL),
("Offer", "C003", "500", NULL, NULL),
("Offer", "C004", "600", NULL, NULL),
("Offer", "C005", "400", NULL, NULL),
("Offer", "C006", "300", NULL, NULL),
("Order", "C001", NULL, "320", NULL),
("Order", "C002", NULL, "180", NULL),
("Order", "C003", NULL, "450", NULL),
("Order", "C004", NULL, "630", NULL),
("Received", "C001", NULL, NULL, "310"),
("Received", "C002", NULL, NULL, "190");

In the table above the purchasing process for different campaigns is displayed using an Event_Type and a Quantity that applies to the Event_Type. Every campaign can only have each Event_Type once.

---

Now, I want ot get the Quantity of the latest available status within the purchasing process for each Campaign.
Summarized the logic is the following:

Received kills Order
Order kills Offer

Therefore, the result should look like this:

Campaign        Event_Type        Quantity
C001              Received          310
C002              Received          190
C003              Order             450
C004              Order             630
C005              Offer             400
C006              Offer             300

In order to achieve this I tried to go with the CASE function but could not make it work:

SELECT
Campaign,
Event_Type,
(CASE 
 WHEN Event_Type = "Order"  THEN SUM(Quantity_Order) 
 WHEN Event_Type = "Offer"  THEN SUM(Quantity_Offer) 
 ELSE SUM(Quantity_Received) 
 END) AS Quantity
FROM Purchasing
GROUP BY 1;

What do I need to change to make it work?

Answer

This is a prioritization query.

In MySQL 8+, use window functions:

select campaign, event_type,
       coalesce(Quantity_Offer, Quantity_Order, Quantity_Received) as quantity
from (select p.*,
             row_number() over (partition by campaign order by field(event_type, 'Received', 'Order', 'Offer')) as seqnum
      from Purchasing p
     ) p
where seqnum = 1;

In earlier versions, a simple method is a correlated subquery:

select campaign, event_type,
       coalesce(Quantity_Offer, Quantity_Order, Quantity_Received) as quantity
from Purchasing p
where event_type = (select p2.event_type
                    from Purchasing p2
                    where p2.campaign = p.campaign
                    order by field(p2.event_type, 'Received', 'Order', 'Offer')
                    limit 1
                   );

If you have a lot of data, you want an index on Purcahsing(campaign, event_type) for this query.

Here is a db<>fiddle.