CODEWITHSUNDEEP
mysql
Snowy007
Snowy007

Reputation: 147

Select all rows with multiple occurrences

Ok, I have a single MySQL table with the name 'test' and 3 columns. 

 ID | playername | lastloginip  
-----------------------------
 1  | user 1     |     1  
 2  | user 2     |     2  
 3  | user 3     |     3  
 4  | user 4     |     4  
 5  | user 5     |     5  
 6  | user 6     |     1  
 7  | user 7     |     1  
 8  | user 8     |     2

Now, I would like to select ALL the rows where the lastloginip is found multiple times in the table, and then give the count of those rows.

In this case, it should return the number 5 as user 1, 2, 6, 7 and 8 have a lastloginip that is found multiple times.

I already tried using 

SELECT COUNT(*) 
FROM (
    SELECT * 
    FROM test 
    GROUP BY lastloginip 
    HAVING COUNT(*) > 1
)  t

But that gave back the number 2 instead of 5.
I am not sure how to set up this query correctly. Most of my findings on the internet keep showing only 2 rows or giving the number 2 instead of 5.

Upvotes: 5

Views: 17594

Answers (3)

Jayant Arora
Jayant Arora

Reputation: 1

You can fetch the sum of occurrences using the above code and if you want to view the records with multiple occurences, refer to the query below-

Select * from test where lastloginip in ( select * from (select lastloginip from test group by lastloginip having count(lastloginip)>1 ) as a)

Upvotes: 0

Lion
Lion

Reputation: 19047

Try this query.

SELECT SUM(loginip) 
FROM(
    SELECT 
    lastloginip, 
    COUNT(lastloginip) 
    as loginip 
    FROM test 
    GROUP BY lastloginip 
    HAVING COUNT(ID)>1
)t

Upvotes: 0

marnir
marnir

Reputation: 1237

First COUNT(), then SUM(): 

SELECT SUM(occurences) 
FROM 
(
   SELECT COUNT(*) AS occurences
   FROM test
   GROUP BY lastloginip
   HAVING COUNT(*)>1
) t

Upvotes: 11

Related Questions