Assign a certain value to all other values if at least one criteria per value is met

Question

CREATE TABLE operations (
    id int auto_increment primary key,
    campaign VARCHAR(255),
    country VARCHAR(255),
    sales_status VARCHAR(255),
    quantity INT
);

INSERT INTO operations
(campaign, country, sales_status, quantity
)
VALUES 
("C001", "DE", "demand", "100"),
("C001", "US", "shipped", "300"),
("C001", "NL", "shipped", "700"),
("C001", "FR", "shipped", "400"),

("C002", "DE", "demand", "500"),
("C002", "US", "demand", "900"),
("C002", "FR", "shipped", "200"),

("C003", "US", "demand", "600"),
("C003", "NL", "demand", "250"),
("C003", "FR", "demand", "150"),
("C003", "PL", "demand", "550"),

("C004", "DE", "shipped", "825"),
("C004", "PL", "shipped", "462");

Expected Result:

campaign    sales_status    SUM(quantity)
C001          shipped             1500
C002          shipped             1600
C003          demand              1550
C004          shipped             1287

In the above result I want that the sales_status per campaign is shipped in case there is at least one country per campaign in which the sales_status is shipped.

For example in campaign C002 only FR is shipped so in the result C002 should be in shipped.

I tried to go with this query:

SELECT
campaign,
(CASE WHEN MAX(sales_status = 'shipped') OVER (PARTITION BY campaign) = 1
THEN 'shipped' ELSE sales_status END) AS sales_status,
SUM(quantity)
FROM operations
GROUP BY 1;

However, it does not give me the correct result.
What do I need to change to make it work?

your code seems overly complicated for what you state you wish to achieve. — P.Salmon, Sep 23 '20 at 06:20
`OVER (PARTITION BY campaign)` makes no sense - you already have GROUP BY by this column. So aggregate version of MAX() is enough. — Akina, Sep 23 '20 at 06:23

score 1 · Answer 1 · answered Sep 23 '20 at 06:19

Why not simple

SELECT
campaign,
MAX(sales_status) AS sales_status,
SUM(quantity)
FROM operations
GROUP BY 1;

?

If there can be another sales_status values you may use something like

SELECT
campaign,
CASE WHEN SUM(sales_status = 'shipped') 
     THEN 'shipped'
     ELSE 'demand' 
     END AS sales_status,
SUM(quantity)
FROM operations
GROUP BY 1;

fiddle

forpas · Accepted Answer · 2020-09-23T06:38:03.763

With conditional aggregation:

SELECT 
  campaign,
  COALESCE(
    MAX(CASE WHEN sales_status = 'shipped' THEN sales_status END), 
    MAX(sales_status)
  ) sales_status,
  SUM(quantity) total
FROM operations
GROUP BY campaign;

Or with window functions FIRST_VALUE() and SUM():

SELECT DISTINCT 
  campaign,
  FIRST_VALUE(sales_status) OVER (PARTITION BY campaign ORDER BY sales_status = 'shipped' DESC) sales_status,
  SUM(quantity) OVER (PARTITION BY campaign) total
FROM operations

See the demo.
Results:

> campaign | sales_status | total
> :------- | :----------- | ----:
> C001     | shipped      |  1500
> C002     | shipped      |  1600
> C003     | demand       |  1550
> C004     | shipped      |  1287

Assign a certain value to all other values if at least one criteria per value is met

2 Answers2