CODEWITHSUNDEEP
mysql
MaZy
MaZy

Reputation: 91

How to find duplicate entries but first group by a column and then count and show it

Hello I cannot solve this. I have a table with user entries and ips. My goal is to find the user 1 and user 2 with same ip.

user_id    | IP
1003       | 1.1.1.1
1003       | 1.1.1.1
1003       | 2.2.2.2
1004       | 1.1.1.1
1004       | 3.3.3.3      
1005       | 4.4.4.4
1005       | 5.5.5.5

What I want is this

user_id    | same_ip_count
1003       | 2
1004       | 2

Here you see 1003 and 1004 showing 2 because both had at least one time same ip.

What I only could get is to show me ALL IPS which had multiple user.

SELECT 
    ip, COUNT(distinct user_id) as used_by_user_count 
FROM 
    user_entry 
WHERE 
    ip != ''
GROUP BY 
    ip
HAVING 
    used_by_user_count > 1

And this is the result

ip         | used_by_user_count
1.1.1.1    | 2

But I couldn't make it work to get user_id instead of ips. Then I tried subqueries (little bit with guesses) but I failed. I hope you can help me.

EDIT: I made quick sqlfiddle http://sqlfiddle.com/#!9/0f8f04/4

Upvotes: 0

Views: 54

Answers (3)

Bill Karwin
Bill Karwin

Reputation: 562651

I would do a self-join from each row to any other row(s) that have the same ip but a different user. By using an inner join, this naturally finds only cases where there are more than one user. So add one to count the first user a.

select a.user_id, count(distinct b.user_id)+1 as count 
from user_entry as a 
join user_entry as b on a.ip = b.ip and a.user_id <> b.user_id 
group by a.user_id;

+---------+-------+
| user_id | count |
+---------+-------+
|    1002 |     2 |
|    1001 |     2 |
+---------+-------+

Upvotes: 2

Paul Spiegel
Paul Spiegel

Reputation: 31812

If the format is not important, you can use GROUP_CONCAT to list all users sharing the same IP:

SELECT 
    ip,
    COUNT(distinct user_id) as used_by_user_count,
    GROUP_CONCAT(distinct user_id) as user_ids
FROM user_entry 
WHERE ip != ''
GROUP BY ip
HAVING used_by_user_count > 1;

Result would be:

|      ip | used_by_user_count |  user_ids |
|---------|--------------------|-----------|
| 1.1.1.1 |                  2 | 1003,1004 |

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133380

for get the user with more that one ip you could use count(distinct ip)

  select user_id, count(distinct ip)
  from user_entry
  group by user_id
  having count(distinct ip) > 1

and for get the user with the same ip you can use 

select a.ip, a.user_id 
   from user_entry a 
   inner join (
      select ip, count(distinct user_id)
      from user_entry
      group by ip
      having count(distinct user_id) > 1
) t on t.ip  = a.ip

Upvotes: 0

Related Questions