Useful Snippets

Welcome!


This blog is used to collect useful snippets related to Linux, PHP, MySQL and more. Feel free to post comments with improvements or questions!

Are your smart devices spying on you? Make better purchasing choices and find products that respect your privacy at Unwanted.cloud

RSS Latest posts from my personal blog


Most viewed posts


Subscribe to RSS feed


Check if column in MySQL table has duplicate values

Stanislav KhromovStanislav Khromov

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
ORDER BY count DESC

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

Source

Web Developer at Aftonbladet (Schibsted Media Group)
Any opinions on this blog are my own and do not reflect the views of my employer.
LinkedIn
Twitter
WordPress.org Profile
Visit my other blog

Comments 15
  • Max Mustermann
    Posted on

    Max Mustermann Max Mustermann

    Reply Author

    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?


  • Max Mustermann
    Posted on

    Max Mustermann Max Mustermann

    Reply Author

    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


    • Stanislav Khromov
      Posted on

      Stanislav Khromov Stanislav Khromov

      Reply 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;
      

    • Vijay Kumar Kanta
      Posted on

      Vijay Kumar Kanta Vijay Kumar Kanta

      Reply Author

      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.


  • Max Mustermann
    Posted on

    Max Mustermann Max Mustermann

    Reply Author

    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?


    • Stanislav Khromov
      Posted on

      Stanislav Khromov Stanislav Khromov

      Reply 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.


  • Max Mustermann
    Posted on

    Max Mustermann Max Mustermann

    Reply Author

    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?


  • Steffen
    Posted on

    Steffen Steffen

    Reply Author

    Very useful. Thank you for sharing! :-)


  • Sean
    Posted on

    Sean Sean

    Reply Author

    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.


  • Dhanabalan S
    Posted on

    Dhanabalan S Dhanabalan S

    Reply Author

    Very good, It worked for me, solved the problem.


  • Pelle
    Posted on

    Pelle Pelle

    Reply Author

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


  • vijay
    Posted on

    vijay vijay

    Reply Author

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


  • joffer
    Posted on

    joffer joffer

    Reply Author

    thanks