CODEWITHSUNDEEP
sqlsql-server
winchesterau
winchesterau

Reputation: 13

Find records that belong to the same identifier, check for multiple occurences of value in column

I have a table which links customer ID's to a sale ID. Multiple customers can be linked the same sale ID, however the first customer should be the Main customer with Type 'M'. All other customers should be type Other ('O').

Cust_ID   Sale_ID  Cust_Type
1         123      'M'
2         123      'O'
3         124      'M'
4         125      'M'
5         125      'O'
6         125      'O'

Sometimes multiple customers linked to the same Sale ID will be the Main ('M') customer - which is not correct:

Cust_ID   Sale_ID  Cust_Type
1         123      'M'
2         123      'M'
3         123      'O'

What I wish to be able to do is return a list of Customer ID's, Sale IDs and Customer Types where more than one of the customers in a sale ID are a main customer. I.e. Main ('M') occurs more than once across rows that have the same sale ID.

Any help is greatly appreciated!

Upvotes: 1

Views: 84

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

So, the problem is that a sales_id can have more than one M value and you want to detect this. I would approach this by using a window function to count those values:

select t.*
from (select t.*,
             sum(case when cust_type = 'M' then 1 else 0 end) over (partition by sales_id) as NumMs
      from table t
     ) t
where NumMs > 1;

Actually, I would use the condition NumMs <> 1, because missing the main customer might also be important.

Upvotes: 3

Pam Lahoud
Pam Lahoud

Reputation: 1105

How about this:

SELECT s.Cust_ID, s.Sale_ID, s.Cust_Type
FROM StackOverflow s INNER JOIN
    (SELECT Sale_ID
    FROM StackOverflow
    WHERE Cust_Type = 'M'
    GROUP BY Sale_ID
    HAVING COUNT(*) > 1) as Multiples ON s.Sale_ID = Multiples.Sale_ID

Upvotes: 0

Felix Pamittan
Felix Pamittan

Reputation: 31879

Is this what you mean? This can be achieved using a window function.

CREATE TABLE temp(
    Cust_ID INT,
    Sale_ID INT,
    Cust_Type VARCHAR(1)
)
INSERT INTO temp VALUES
(1, 123, 'M'),
(2, 123, 'M'),
(3, 124, 'M'),
(4, 125, 'M'),
(5, 125, 'O'),
(6, 125, 'O');


WITH CTE AS(
    SELECT *, cc = COUNT(*) OVER(PARTITION BY Sale_ID)
    FROM temp 
    WHERE Cust_Type = 'M'
)
SELECT
    Cust_ID,
    Sale_ID,
    Cust_Type
FROM CTE
WHERE cc > 1

DROP TABLE temp

Upvotes: 0

Related Questions