How to count duplicates in a row PHP MYSQL

Question

I have a usage database that i use to store some info whenever a user logs in.

So for each login the following is stored

rid (auto integer)
id (auto integer from the users database where the users details are stored login, contact details ect.)
username
password
date (y-m-d)

Is there a way to count the number of times that a duplicate id appears in the database and ORDER by DESC.

What I want is to tell who is the most active user by counting how many times the same id appears in the database.

Kleskowy · Accepted Answer · 2014-10-03 08:35:45Z

0

Try this query (as Tal Gleicher provided an errorous one, good thinking though):

SELECT id, COUNT(*) as login_count FROM `tbl` 
GROUP BY id 
ORDER BY login_count DESC

You can set a limit for that query, for example if you want to get top 10 most active users' ids:

SELECT id, COUNT(*) as login_count FROM `tbl` 
GROUP BY id 
ORDER BY login_count DESC 
LIMIT 0, 10

answered Oct 3, 2014 at 8:35

Kleskowy

2,6681 gold badge18 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Programmer IPls · Accepted Answer · 2022-05-22 13:27:51Z

0

SELECT id ,COUNT(*) 
FROM usageTable
GROUP BY id 
ORDER BY COUNT(*) DESC;

This will display a list:

10 | 203
2 | 100
5 | 83

answered May 22, 2022 at 13:22

Programmer IPls

94 bronze badges

Jentel · Accepted Answer · 2014-10-03 07:13:20Z

-1

mysql -> SELECT count(rid) FROM `tbl` ORDER BY rid DESC GROUP BY id

It will show you the number of activities for each id

answered Oct 3, 2014 at 7:13

Jentel

1542 silver badges19 bronze badges