Check if values in one column are not unique based on values in other column

DB-Fiddle

CREATE TABLE logistics (
    id int auto_increment primary key,
    campaign VARCHAR(255),
    process_status VARCHAR(255),
    delivery_type VARCHAR(255),
    quantity INT
);

INSERT INTO logistics
(campaign, process_status, delivery_type, quantity
)
VALUES 
("C001", "offer", "fedex", "500"),
("C001", "offer", "fedex", "200"),
("C001", "order", "ubs", "400"),
("C001", "shipped", "ubs", "100"),

("C002", "offer", "fedex", "800"),
("C002", "offer", "fedex", "100"),
("C002", "order", "fedex", "650"),
("C002", "shipped", "fedex", "250"),

("C003", "offer", "fedex", "730"),
("C003", "order", "fedex", "620"),
("C003", "shipped", "dhl", "530"),

("C004", "order", "fedex", "200"),
("C004", "shipped", "fedex", "130"),

("C005", "order", "fedex", "900"),
("C005", "shipped", "ubs", "475"),

("C006", "order", "fedex", "635");

Expected Result

campaign      process_status       delivery_type      quantity
C001          offer                fedex              700
C001          order                ubs                400
C001          shipped              ubs                100
C003          offer                fedex              730
C003          order                fedex              620        
C003          shipped              dhl                530
C005          offer                fedex              900
C005          shipped              ubs                475

In the above table I have different campaigns, their process_status and delivery_type.
Now, I want to extract all campaigns which switch their delivery_type between different process_status.

Therefore, I am looking for a query that does something like this:

SELECT
campaign,
process_status,
delivery_type,
quantity
FROM logistics
WHERE delivery_type differes in different process_status

I have no clue how the WHERE condition must look to achieve the expected result.
Do you have any idea?

Answer

You can try the below query.

SELECT 
campaign, process_status, delivery_type, sum(quantity) as quantity
FROM logistics
WHERE campaign IN (
    SELECT
    campaign
    FROM logistics
    GROUP BY campaign
    HAVING max(delivery_type)!=min(delivery_type)
)
GROUP BY campaign, process_status, delivery_type

Check Demo Here

Advertisement

Answer