Check if column in MySQL table has duplicate values

So you have a column that is not UNIQUE, but you’d still like to check if there is duplicate values in it.

If your column name is my_column in table my_table, the query is:

SELECT my_column, COUNT(*) as count
FROM my_table
GROUP BY my_column
HAVING COUNT(*) > 1

This will return all records that have duplicate my_column content, as well as how many times this content occurs in the database.

Source

14 thoughts on “Check if column in MySQL table has duplicate values

  1. Max Mustermann

    Thanks for sharing this query. But how to show not only the related column and the could, but list the columns that are dupe? For example, the table has 2 columns ‘path’ and ‘folder’. Your query would list either ‘path’ or ‘folder’ and the count. But how can it list CONCAT(path,folder) to list the different paths?

    Reply
  2. Max Mustermann

    Tested and found this query to not find dupes anymore. I tried to fix it by adding the 'HAVING COUNT(*) > 1' contition with no luck

    Reply
    1. Stanislav Khromov Post author

      This works fine for me to detect entries with at least one duplicate:

      SELECT CONCAT(path,folder), COUNT(*) as count FROM your_table GROUP BY path,folder HAVING COUNT(*) > 1;
      
      Reply
    2. Vijay Kumar Kanta

      This worked flawlessly. Do not forget to use the same column for *SELECT*, *HAVING* and *COUNT* keyword/clauses. That was the mistake I was doing when the query wasn’t working. Not anymore.

      Reply
  3. Max Mustermann

    I suppose the reason within the fact that this query compares the full path rather than the directory only. I aim to find dupe folders no matter which path is prepended and get the full path + folder listed.
    Is that possible?

    Reply
    1. Stanislav Khromov Post author

      The query I wrote does compare the full path. It’s not really clear to me what you are after, you would have to explain better, give an example and preferably also the schema you use.

      Reply
  4. Max Mustermann

    It’s not really clear to me what you are after

    Ok, in short:

    1 Table, 3 Columns (id, path, folder)

    I am looking to find duplicates in the ‘folder’ column – duplicate folder names ONLY and want the output to show the full path to the duplicate folder (concate path + folder). There cannot be any duplicates when concatenating path and folder as this would always be the same folder which is why your 2nd query doesn’t find any duplciates in my table. But there can be duplicate folders at different paths. So the query must search for duplicates in the folder column and list both the path and folder may it be concatenated or separate so i can decide which folder to delete.

    Adopting your initial query to my case this query lists duplicate folders and their duplicate count.

    Now clearer?

    Reply
  5. Sean

    Max you could use union to get the folder and path

    SELECT folder, COUNT(*) as count FROM your_table GROUP BY folder
    union
    SELECT * FROM your_table GROUP BY folder

    This should allow you to get the count from folder but also the path.

    Reply
  6. Pelle

    I get the error 2001: The column ‘COUNT’ cannot be found in the table ‘…’ any ideas?

    Reply
    1. Stanislav Khromov Post author

      MySQL? Which version? Make sure you copied the snippet correctly.

      Reply
  7. vijay

    i need script , i want to open website on unique ip , if duplicate ip found , site will not open or redirect. plz help

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Markdown is allowed in comments.