How to get the customers who has multiple product values-CodePudding

I have a table with 2 columns table(cust, prod)

Below is the table

cust                prod
1100000000104440000 PRODUCT_1
1100000000083280000 PRODUCT_1
1100000000104440000 PRODUCT_2
1000000000000950000 PRODUCT_2

I'm trying to get the customer who bought more than one product. The expected output must be

cust                prod
1100000000104440000 PRODUCT_1
1100000000104440000 PRODUCT_2

I wrote below SQL query, but it's not working

WHERE table.prod IN ('PRODUCT_1', 'PRODUCT_2')

Can anyone help me with this?

CodePudding user response：

This is the solution for mysql, you can modify and make it compatible with ansi-sql

SELECT table1.* FROM table1
JOIN
    (SELECT table1.cust, COUNT(table1.prod) FROM table1
    GROUP BY cust
    HAVING COUNT(table1.prod) > 1)
as a ON a.cust = table1.cust

Check out this db fiddle

CodePudding user response：

Using QUALIFY and windowed COUNT:

SELECT *
FROM table
QUALIFY COUNT(DISTINCT prod) OVER(PARTITION BY cust) > 1;