CODEWITHSUNDEEP
phpmysqlsql
Khureem
Khureem

Reputation: 65

SQL Statement to select duplicate records appearing more than 2 times

I need help to get the solution for this condition. I have a table containing records, there is a field sku, in this record i have sku's appearing multiple times. Table structure is like this rid|id|sku|name

rid is auto_increment, where is id is varchar, if any sku is available on table multiple times the record looks like this

rid  id  sku     name
---  --  ------  --------------
1    3   rs-123  test product
2    3   rs-123  test product
3    4   rs-125  test product 2
4    4   rs-125  test product 2
5    4   rs-125  test product 2
6    6   rs-126  test product 3

I used this sql statement to get records that appears only once

SELECT *
FROM test
GROUP BY id
HAVING ( COUNT(id) = 1 )

This brings the records that are only added once, so according to above give record only rid 6 is the output

I tried to modify the above code to this to get the result of the records which are added 2 times

    SELECT * FROM test 
GROUP BY id 
HAVING ( COUNT(id) = 2 )

The result I am getting is of those record which are added 2 times, but the issue is the output is appearing only 1 record like this;

rid  id  sku     name
---  --  ------  ------------
1    3   rs-123  test product

I need to fetch all rows of record that are added 2 times in the database. Please help

Upvotes: 5

Views: 23975

Answers (5)

spencer7593
spencer7593

Reputation: 108370

SELECT t.rid
     , t.id
     , t.sku
     , t.name
  FROM test t
  JOIN ( SELECT s.sku 
           FROM test s 
          GROUP BY s.sku
         HAVING COUNT(1) > 1
       ) d
    ON d.sku = t.sku

The inline view aliased as d returns the sku values that appear more than once in the table. We can join the results of that query to the table to get all rows that have a sku that matches.

Are id and sku interchangeable? That wasn't clear to me. (If id is dependent on sku and sku is dependent on id, then you can replace references to sku with references to id in that query.

Upvotes: 8

Jonathan Leffler
Jonathan Leffler

Reputation: 753475

Test Driven Query Design — TDQD — to the fore.

Find the SKUs that appear more than once

SELECT sku
  FROM test
 GROUP BY sku
HAVING COUNT(*) > 1

Find the details for all the rows where the SKU appears more than once

SELECT t.*
  FROM test AS t
  JOIN (SELECT sku
          FROM test
         GROUP BY sku
        HAVING COUNT(*) > 1
       ) AS s
    ON t.sku = s.sku

Upvotes: 5

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

When you group by Id you can't get more than one Id in the group thus COUNT(Id) is always 1. Use this instead

SELECT * FROM test 
GROUP BY id 
HAVING ( COUNT(sku) = 2 )

If you want to get all records with more than one duplicate sku use this:

SELECT * FROM test 
GROUP BY id 
HAVING ( COUNT(sku) > 1 )

Upvotes: 0

Strawberry
Strawberry

Reputation: 33935

CREATE TABLE test
(rid INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,id INT NOT NULL
,sku VARCHAR(12) NOT NULL
,name VARCHAR(20) NOT NULL
);

INSERT INTO test VALUES 
(1,3,'rs-123','test product'),
(2,3,'rs-123','test product'),
(3,4,'rs-125','test product 2'),
(4,4,'rs-125','test product 2'),
(5,4,'rs-125','test product 2'),
(6,6,'rs-126','test product 3');

SELECT x.* FROM test x JOIN test y ON y.id = x.id GROUP BY x.rid HAVING COUNT(*) > 1;
+-----+----+--------+----------------+
| rid | id | sku    | name           |
+-----+----+--------+----------------+
|   1 |  3 | rs-123 | test product   |
|   2 |  3 | rs-123 | test product   |
|   3 |  4 | rs-125 | test product 2 |
|   4 |  4 | rs-125 | test product 2 |
|   5 |  4 | rs-125 | test product 2 |
+-----+----+--------+----------------+
5 rows in set (0.01 sec)

Upvotes: 3

take
take

Reputation: 2222

This query should work for you:

SELECT * FROM test WHERE 
   id IN(SELECT id FROM test group by id HAVING count(id) > 1)

Upvotes: 0

Related Questions