Coalesce columns and replace NULL value

Question

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?

I think you should go back to your previous schema. I preferred it. — Strawberry
– Strawberry, Commented Mar 9, 2020 at 11:57

Yogesh Sharma · Accepted Answer · 2020-03-09 12:01:14Z

1

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;

answered Mar 9, 2020 at 12:01

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Barbaros Özhan · Accepted Answer · 2020-03-09 12:25:05Z

1

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

edited Mar 9, 2020 at 12:25

answered Mar 9, 2020 at 12:19

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2020-03-09 12:18:28Z

0

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);

edited Mar 9, 2020 at 12:18

answered Mar 9, 2020 at 11:57

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Michi Over a year ago

Thanks for your answer. I assume this only works with mySQL 8.0 but I only have mySQL 5.7. Is there also a solution for older versions?

Strawberry · Accepted Answer · 2020-03-09 12:04:32Z

0

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      |
+----+----------+------------+------------+----------+

answered Mar 9, 2020 at 12:04

Strawberry

34k14 gold badges43 silver badges57 bronze badges

Collectives™ on Stack Overflow

Coalesce columns and replace NULL value

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related