CODEWITHSUNDEEP
mysqlgroup-by
user3649739
user3649739

Reputation: 1869

Find duplicate records with different values

I know how to query to find records with duplicate records based on a field or fields e.g.

Select Customer,Count(*) from Table1 group by Customer,Month having count(*)>1

which would give me a list of all customers who ordered more than once in a given month.

However from that select I'd like to:

I am trying to return:

Bob,Widget,December
Bob,Pipes,December
Mary,Books,June
Mary,Cars,June

Upvotes: 0

Views: 34

Answers (1)

Shadow
Shadow

Reputation: 34294

If product field is in the same table, then you can use count with distinct on the product field to get the number of distinct products:

Select Customer, Month, Count(distinct product)
from Table1
group by Customer, Month
having count(distinct product)>1

If you want to know what they ordered, then join it back as a subquery to your main table:

select distinct t1.customer, t1.month, t1.product from table1 t1
inner join
    (Select Customer, Month, Count(distinct product)
     from Table1
     group by Customer, Month
     having count(distinct product)>1
    ) t2 on t1.customer=t2.customer and t1.month=t2.month

The distinct in the outer select depends on your exact needs.

Upvotes: 1

Related Questions