CODEWITHSUNDEEP
sqlsql-serverdatabaset-sql
Emitsu
Emitsu

Reputation: 31

Remove duplicate value on one column based on value of other column

I have a table called Table1 see below

PRODUCT    CUSTOMER               COMP
DICE       DAVES PET SHOP         Billed
DICE       CLAXTONS               ToT
CARDS      VIEWSONIC              NITS
CARDS      NORTHERN LIGHTS        Billed
CARDS      NORTHERN LIGHTS        NITS
BOX        TABLEAU                Billed
BOX        TABLEAU                ToT

There are some values where there is a duplicate in the CUSTOMER field but it will always contain Billed in at least one of the COMP sections if it is a duplicate, so I want the resulting query to only return the value in COMP where it is Billed for the duplicates so the resulting table would look like

PRODUCT    CUSTOMER               COMP
DICE       DAVES PET SHOP         Billed
DICE       CLAXTONS               ToT
CARDS      VIEWSONIC              NITS
CARDS      NORTHERN LIGHTS        Billed
BOX        TABLEAU                Billed

Here is the SQL I tried

SELECT * 
FROM Table1
WHERE COMP = 'Billed'
UNION ALL
SELECT Table1_A.PRODUCT, Table1_A.CUSTOMER, Table1_A.COMP
FROM Table1 Table1_A
LEFT JOIN (
    SELECT * 
    FROM Table1 
    WHERE COMP != 'Billed'
) Table1_B ON Table1_B.PRODUCT = Table1_A.PRODUCT 
    AND Table1_B.CUSTOMER = Table1_A.CUSTOMER

I thought if I put in a UNION and referenced both equal to Billed and not equal to Billed, then it would keep the format I'm looking for but it just repeated every single CUSTOMER for every single COMP

Upvotes: 0

Views: 285

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269663

You could use aggregation for your rules:

SELECT PRODUCT, CUSTOMER, 
       (CASE WHEN COUNT(*) = 1 THEN MAX(COMP) ELSE 'Billed' END)
FROM table1 
GROUP BY PRODUCT, CUSTOMER;

You can also use a UNION ALL approach:

select product, customer, comp
from table1 t1
where comp = 'Billed'
union all
select product, customer, comp
from table1 t1
where not exists (select 1
                  from table1 tt1
                  where tt1.product = t1.product and
                        tt1.customer = t1.customer and
                        tt1.comp = 'Billed'
                 );

Upvotes: 1

Related Questions