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?
Advertisement
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.