Find duplicate values in MySQL based on combination criteria

Question

Need: To get the count of duplicates in a table. It is considered as duplicate when the order number is same but date is different. Note that the combination of Date, Order No. & Tech No. is unique in the table.

Research: I referred to this solution which is close to my need Link1. But in this case the duplicate criteria depends on combination of date & Order no.

Hope my query is clear

Table:

   Date    | Order No.| TechNo. | Company  | Price
---------------------------------------------------
2018-04-12 |   12345  | 987     | CompanyA | 432
2018-04-12 |   12345  | 991     | CompanyA | 442
2018-05-13 |   23456  | 345     | CompanyB | 832
2018-06-14 |   34567  | 656     | CompanyC | 932
2018-07-15 |   12345  | 890     | CompanyD | 132
2018-08-18 |   12345  | 678     | CompanyA | 932

Expected query output:

Order No. | Count
-------------------
12345     | 3

Edit:

Have updated the table to reflect the actual scenario. Apologies to the forum & moderators missed out the column - TechNo. in the initial post.

tawab_shakeel · Accepted Answer · 2019-06-30T06:37:13.290

2

try this I hope it would solve your problem

select order_no,count(*) from (select distinct date,order_no from Test) a
 group by order_no having count(*) > 1;

edited Jun 30 '19 at 06:37

answered Jun 30 '19 at 05:46

tawab_shakeel

3,701
10
26

Kindly read my query again. The criteria for duplicate is combination not just order no. alone. – iCoder Jun 30 '19 at 06:11
@iCoder you also mention that table contains unique in combination with date and order no, so order no with same date cannot repeat – tawab_shakeel Jun 30 '19 at 06:12
Sorry for that my sample did not contain another column. I will update my query to reflect the situation. Apologies – iCoder Jun 30 '19 at 06:15
@iCoder check my updated result I have described the answer – tawab_shakeel Jun 30 '19 at 06:17
Please check the updated query now. It contains the column which I missed out in the initial post – iCoder Jun 30 '19 at 06:23
1

Fantastic. Thank you very much & apologies again for not stating my query properly initially. – iCoder Jun 30 '19 at 06:44
@iCoder no problem at all. – tawab_shakeel Jun 30 '19 at 06:45

Find duplicate values in MySQL based on combination criteria

1 Answers1