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


hello cannot solve this. have table user entries , ips. goal find user 1 , user 2 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 want this

user_id    | same_ip_count 1003       | 2 1004       | 2 

here see 1003 , 1004 showing 2 because both had @ least 1 time same ip.

what show me ips had multiple user.

select      ip, count(distinct user_id) used_by_user_count       user_entry       ip != '' group      ip having      used_by_user_count > 1 

and result

ip         | used_by_user_count 1.1.1.1    | 2 

but couldn't make work user_id instead of ips. tried subqueries (little bit guesses) failed. hope can me.

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

i self-join each row other row(s) have same ip different user. using inner join, naturally finds cases there more 1 user. add 1 count first user a.

select a.user_id, count(distinct b.user_id)+1 count  user_entry  join user_entry b on a.ip = b.ip , a.user_id <> b.user_id  group a.user_id;  +---------+-------+ | user_id | count | +---------+-------+ |    1002 |     2 | |    1001 |     2 | +---------+-------+ 

Comments

Popular posts from this blog

android - InAppBilling registering BroadcastReceiver in AndroidManifest -

python Tkinter Capturing keyboard events save as one single string -

sql server - Why does Linq-to-SQL add unnecessary COUNT()? -