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

DB-Fiddle

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?

Answer

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