MySQL check if value exist more than once

Question

I have MySQL database and a have a table named CALLER. In table caller I need to check if in column USERNAME there are values which exist more than once and if exist to list all these values.

Thank you

Answer 1

Ususlly you can do this using GROUP BY and HAVING COUNT(*)>1

SELECT USERNAME FROM CALLER
GROUP BY USERNAME 
HAVING COUNT(*)>1

Update: To get all duplicate rows:

SELECT * FROM CALLER WHERE USERNAME IN
  ( 
    SELECT USERNAME FROM CALLER
    GROUP BY USERNAME 
    HAVING COUNT(*)>1
  )

Answer 2

Before getting to the answer....

It would have been a help if you'd provided the full table structure (as a CREATE TABLE statement)

If you need to apply this exercise it implies your database design is wrong - and after you've identified the duplicates and resolved them then you should add a unique index on the relevant column.

Assuming that you've got an auto-increment field, or some other value (such as created date) which differentiates between rows with the same USERNAME....

 SELECT a.id, username, b.id
 FROM caller a
 INNER JOIN caller b
 ON a.username=b.username
 AND b.id>a.id

Note that this will report some rows more than once if the username exists for more than 2 rows. Alternately:

SELECT username, COUNT(DISTINCT id), MIN(id), MAX(id)
FROM caller
GROUP BY username
HAVING count(*)>1

But this won't explicitly identify all the ids where there are more than 2 rows with a specific username.