CODEWITHSUNDEEP
mysqlgroup-bycount
Steve
Steve

Reputation: 2586

Ability to count repeated row IDs in a MySQL table

I am trying to count all repeated IDs within a MySQL table.

Here is the table which I generate after running this query

   SELECT
      aid, uid, timestamp
    FROM
      login_activity
    where uid <> 0
    order by uid


aid | uid | timestamp
----------------------- 
17  |  43  | 1507677429
63  |  73  | 1507677429
101 |  91  | 1507677429
45  |  109 | 1507677429
1   |  423 | 1507677429
3   |  423 | 1507677429    
23  |  431 | 1507677429
19  |  431 | 1507677429
8   |  431 | 1507677429
7   |  445 | 1507677429
6   |  445 | 1507677429
33  |  445 | 1507677429
9   |  445 | 1507677429

Here I want to get 3 as there are three instances where uid is repeated. These are 423, 431and 445. We will discard 43, 73, 91, 109 as they are not repeated.

Upvotes: 0

Views: 19

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

There are several ways to do this. I would recommend starting with two aggregations:

select count(*)
from (select uid
      from login_activity
      where uid <> 0
      group by uid
      having count(*) > 1
     ) la;

An alternative with only one aggregation uses filtering (and assumes that aid/uid is unique):

select count(distinct la.uid)
from login_activity la
where exists (select 1
              from login_activity la2
              where la2.uid = la.uid and la2.aid <> la.aid
             );

Upvotes: 1

Related Questions