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

Question

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?

nicely explained question and thumbs up for the DB fiddle and DDL and DML within the question — Shoaeb, May 06 '20 at 07:53
I think it may take time to reopen. You can check this [DB Fiddle](https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=71210526c689a8c98798b6590baaf8bf) for the answer. I will post it when it is reopened. @Michi — Arun Palanisamy, May 06 '20 at 08:58

score 0 · Answer 1 · edited May 17 '20 at 00:11

0

This will give you all campaigns where delivery type different in different process states:

SELECT (campaign),count(1) AS cnt
FROM logistics
GROUP BY campaign,
         delivery_type
HAVING cnt>1;

You can add distinct if you want unique campaign values:

SELECT distinct(campaign),
       count(1) AS cnt
FROM logistics
GROUP BY campaign,
         delivery_type
HAVING cnt>1;

edited May 17 '20 at 00:11

Nimantha

6,405
6
28
69

answered May 06 '20 at 07:51

Shoaeb

709
7
18

score 0 · Accepted Answer · answered May 06 '20 at 13:14

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

score 0 · Answer 3 · answered May 06 '20 at 13:55

DB-Fiddle

/* Match all values to campaigns that fullfill condition */
SELECT
l2.campaign,
l2.process_status,
l2.delivery_type,
SUM(l2.quantity)
from logistics l2
JOIN

  /* Get campaigns that fullfill condition */
   (SELECT
   t1.campaign,
   t1.process_status,
   t1.delivery_type,
   t1.count_process_status,
   t1.count_delivery_status
   FROM

     /* Count process_status and delivery_status */
     (SELECT
     l.campaign,
     l.process_status,
     l.delivery_type,
     COUNT(DISTINCT l.process_status) as count_process_status,
     COUNT(DISTINCT l.delivery_type) as count_delivery_status,
     SUM(l.quantity)
     FROM logistics l
     GROUP BY 1) t1

    WHERE count_delivery_status > 1) t2 ON l2.campaign = t2.campaign

GROUP BY 1,2,3;

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

3 Answers3