CODEWITHSUNDEEP
mysqlsql
Michi
Michi

Reputation: 5471

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?

Upvotes: 0

Views: 79

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1270553

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.

Upvotes: 1

Strawberry
Strawberry

Reputation: 33945

SELECT x.campaign
     , COALESCE(z.event_type,y.event_type,x.event_type) event_type
     , COALESCE(z.quantity_received,y.quantity_order,x.quantity_offer) quantity
  FROM purchasing x
  LEFT 
  JOIN purchasing y 
    ON y.campaign = x.campaign 
   AND y.event_type = 'order'
  LEFT 
  JOIN purchasing z 
    ON z.campaign = y.campaign 
   AND z.event_type = 'received'
WHERE x.event_type = 'offer'

http://www.sqlfiddle.com/#!9/2b9394/37

Upvotes: 1

nbk
nbk

Reputation: 49395

Use this for your query.

this is based you first make your offer, then comes tghe order and at last comes the received, when 1 of them in that order is empty, then 1 prior must be the current state.

SELECT
Campaign,
IF(QReceived IS NOT NULL, "Received",
   IF(QOrder IS NOT NULL, "Order",
      IF(QOffer IS NOT NULL, "Offer", NULL))) Event_Type,
IF(QReceived IS NOT NULL, QReceived,
   IF(QOrder IS NOT NULL, QOrder,
      IF(QOffer IS NOT NULL, QOffer, NULL))) qty
      FROM
(SELECT 
 Campaign,
SUM(Quantity_Received) QReceived,

   SUM(Quantity_Order) QOrder,
   SUM(Quantity_Offer) QOffer

FROm Purchasing
GROUP BY Campaign) t1

Which gives you foolowing result

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

http://www.sqlfiddle.com/#!9/2b9394/34

Upvotes: 1

Paul Spiegel
Paul Spiegel

Reputation: 31812

Since by string comparison 'Offer' < 'Order' < 'Received', you can use the classic way with a group by subquery, to find the "latest" row:

select 
  p.Campaign,
  p.Event_Type,
  case p.Event_Type
    when 'Order' then p.Quantity_Order
    when 'Offer' then p.Quantity_Offer
    when 'Received' then p.Quantity_Received
  end as Quantity
from (
  select Campaign, max(Event_Type) as Event_Type
  from Purchasing
  group by Campaign
) mx
natural join Purchasing p
order by p.Campaign

Result:

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

View on DB Fiddle

But if you were not so lucky with your Event_Type values, or if you want a more solid (more reliable) solution, you would need to define a custom order with either FIELD(), FIND_IN_SET(), a case statement, or a sort mapping (derived) table. Here a way using FIELD():

select 
  p.Campaign,
  p.Event_Type,
  case p.Event_Type
    when 'Order' then p.Quantity_Order
    when 'Offer' then p.Quantity_Offer
    when 'Received' then p.Quantity_Received
  end as Quantity
from Purchasing p
where p.Event_Type = (
  select p1.Event_Type
  from Purchasing p1
  where p1.Campaign = p.Campaign
  order by field(p1.Event_Type, 'Offer', 'Order', 'Received') desc
  limit 1
)
order by p.Campaign

View on DB Fiddle

Upvotes: 1

Related Questions