CODEWITHSUNDEEP
mysqlsql
Michi
Michi

Reputation: 5471

Coalesce columns and replace NULL value

DB-Fiddle

CREATE TABLE logistics (
    id int primary key,
    campaign VARCHAR(255),
    event_type VARCHAR (255),
    date_offered DATE,
    date_ordered DATE, 
    date_delivered DATE,
    quantity VARCHAR(255)
);

INSERT INTO logistics
(id, campaign, event_type, 
date_offered, date_ordered, date_delivered, quantity
)
VALUES 
("1", "C001", "offered", "2019-04-10", NULL, NULL, "500"),
("2", "C001", "ordered", NULL, "2019-04-16", NULL, "450"),
("3", "C001", "stored", NULL, NULL, "2019-04-18", NULL),

("4", "C002", "offered", "2019-08-14", NULL, NULL, "700"),
("5", "C002", "ordered", NULL, "2019-09-04", NULL, "730"),
("6", "C002", "stored", NULL, NULL, "2019-09-15", "800");

I want to run a query that coalesce the values so:

a) all date values are in one column called event_date and
b) in case there is no quantity for the event_type stored (as you can see for C001) the quantity of the event_type order should be used.

The result should look like this:

campaign     event_type       event_date         quantity
C001         offered          2019-04-10          500
C001         ordered          2019-04-16          450
C001         stored           2019-04-18          450
C002         offered          2019-08-14          700
C002         ordered          2019-09-04          730
C002         stored           2019-09-15          800

I tried to go with this:

SELECT id,
       campaign,
       event_type,
       coalesce(date_offered, date_ordered, date_delivered) as event_date,
       quantity
  FROM logistics;

With this query I get close to my expected results but for C001 there is a NULL for C001 in the event_type stored.

How do I have to modify my query to get the quantity of the event_type ordered for the event_type stored for C001?

Upvotes: 3

Views: 446

Answers (4)

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

You can use a logic similar to self join for the table logistics where the part after JOIN clause would be a nested query filtered out through event_type = 'ordered' and grouped by campaign column :

SELECT id, l1.campaign, event_type,
       COALESCE(date_offered, date_ordered, date_delivered) AS event_date,
       COALESCE(l1.quantity,l2.quantity) AS quantity 
  FROM logistics l1
  JOIN ( SELECT campaign, MAX(quantity) AS quantity 
           FROM logistics 
          WHERE event_type = 'ordered' 
          GROUP BY campaign  )  l2
    ON l2.campaign = l1.campaign

Demo

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You can use window functions:

SELECT id, campaign, event_type,
       coalesce(date_offered, date_ordered, date_delivered) as event_date,
       coalesce(quantity,
                max(case when event_type = 'ordered' then quantity end) over (partition by campaign)
               ) as new_quantity
FROM logistics;

In older versions, you can use a JOIN:

SELECT l.id, l.campaign, l.event_type,
       coalesce(l.date_offered, l.date_ordered, l.date_delivered) as event_date,
       coalesce(l.quantity, lo.quantity
               ) as new_quantity
FROM logistics l LEFT JOIN
     (SELECT lo.campaign, MAX(lo.quantity) as quantity
      FROM logistics lo
      WHERE lo.event_type = 'ordered'
      GROUP BY lo.campaign
     ) lo
     USING (campaign);

Upvotes: 0

Strawberry
Strawberry

Reputation: 33935

FWIW, this strikes me as a better schema:

+----+----------+------------+------------+----------+
| id | campaign | event_type | event_date | quantity |
+----+----------+------------+------------+----------+
|  1 |    1     | offered    | 2019-04-10 | 500      |
|  2 |    2     | offered    | 2019-08-14 | 700      |
|  3 |    1     | ordered    | 2019-04-16 | 450      |
|  4 |    2     | ordered    | 2019-09-04 | 730      |
|  5 |    1     | stored     | 2019-04-18 | NULL     |
|  6 |    2     | stored     | 2019-09-15 | 800      |
+----+----------+------------+------------+----------+

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use correlated sub-query : 

SELECT l.id, l.campaign, l.event_type,
       coalesce(l.date_offered, l.date_ordered, l.date_delivered) as event_date,
       coalesce(l.quantity,
                      (SELECT MAX(l1.quantity)
                       FROM logistics l1
                       WHERE l1.event_type = 'ordered' AND l.campaign = l1.campaign
                      )
               ) as new_quantity
FROM logistics l;

Upvotes: 1

Related Questions