How to identify multiples in table with SQL?-CodePudding

Here is how my data is configured in my table that regroups all orders placed by customers:

I have 3 fields: ORDER_ID, that is the unique field per ORDER (only appears once in the whole table), USER_KEY, that is the unique field per BUYER (buyers can place multiple orders and can appear multiple times in the order table), and FRUIT, that identifies the item that has been bought per order.

I need to identify the buyers (USER_KEY), that have purchases multiples of the same products.

What kind of SQL query would allow me to see this?

CodePudding user response：

A little hard for me to tell if you want users that have multiple orders with the same fruit or if you just want users with multiple orders:

    declare @temp table (
    order_id int,
    user_key int,
    fruit varchar(20)
)

INSERT INTO @temp select 4653, 1111,'Apple'
INSERT INTO @temp select 5876, 2222,'Banana'
INSERT INTO @temp select 5678, 1111,'Orange'
INSERT INTO @temp select 9875, 1111,'Apple'
INSERT INTO @temp select 6547, 3333,'Apple'
INSERT INTO @temp select 3478, 4444,'Kiwi'
INSERT INTO @temp select 7621, 5555,'Orange'
INSERT INTO @temp select 8907, 6666,'Banana'
INSERT INTO @temp select 3678, 6666,'Orange'

select 
    t.user_key
from @temp t
inner join (
    SELECT user_key,
    Count(order_id) as [Minimum of 2 Count] from @temp group by user_key, fruit 
) it on t.user_key = it.user_key and it.[Minimum of 2 Count] > 1
group by t.user_key

This will bring back 1111 as a user having 2 orders with the same fruit on them.