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