CODEWITHSUNDEEP
mysql
rahul
rahul

Reputation: 31

How to find most repeating element from a table?

I have a table tbl_user and I want to find which user_name(s) have the most repeating values in the table. I only want to retrieve those user_id(s).

I also need a separate query to find the count the of duplicate user_name(s) in the table .

Upvotes: 3

Views: 227

Answers (5)

Gus
Gus

Reputation: 7349

You could select the user_names which are duplicated with this SQL:

SELECT COUNT(1) AS duplicates, user_name
FROM tbl_user
GROUP BY user_name
HAVING duplicates > 1

Having done this, you can then use it as a subquery to find out the count of duplicate user_names

SELECT COUNT(1) AS count_of_user_names, SUM(duplicates) AS duplicate_records
FROM (
  SELECT COUNT(1) AS duplicates, user_name
  FROM tbl_user
  GROUP BY user_name
  HAVING duplicates > 1
) AS subquery

Or use it to find out the user ids:

SELECT user_id
FROM tbl_user
WHERE user_name IN (
  SELECT user_name
  FROM tbl_user
  GROUP BY user_name
  HAVING COUNT(1) > 1
)

Upvotes: 0

hsz
hsz

Reputation: 152294

First query to find the biggest amount of repeated usernames:

  SELECT user_name, COUNT(user_id) AS amount
    FROM tbl_user
GROUP BY user_name
ORDER BY amount DESC

And then grab id`s:

# faster way using username from last query

  SELECT user_id
    FROM tbl_user
   WHERE user_name = '$user_name'


# longer way

  SELECT user_id
    FROM tbl_user
   WHERE user_name = (
       SELECT user_name
         FROM tbl_user
     GROUP BY user_name
     ORDER BY COUNT(amount) DESC
        LIMIT 1
   )

Upvotes: 1

Jaydee
Jaydee

Reputation: 4158

If you mean you want to count the occurences of a particular username, something like this may work for you.

select user_name,user_id,count(*) as mycount from tbl_user group by user_name order by mycount desc limit 5

This should get the top 5 entries

Upvotes: 0

Michael B
Michael B

Reputation: 1763

SELECT COUNT(*) FROM tbl_user
GROUP BY tbl_user.user_name

Will give you the number of duplicate user_names. You can use a scripting language to select out the highest one if you like, or just look at the list if it's not something you need automated.

Upvotes: 0

Tudor Constantin
Tudor Constantin

Reputation: 26871

To find the count of duplicates:

SELECT count(id) AS num_duplicates FROM tbl_users GROUP BY user_name ORDER BY num_duplicates DESC

To find the user_id's of the most duplicated user_name:

SELECT user_id FROM tbl_user 
    WHERE username IN ( SELECT user_id 
                           FROM tbl_users 
                        GROUP BY user_name 
                        ORDER BY count(id) DESC LIMIT 0, 1)

Upvotes: 0

Related Questions