CODEWITHSUNDEEP
mysqlsqldatabase
Seldimi
Seldimi

Reputation: 97

Get duplicate users from orders table

I am trying to find a way to detect the users who have duplicate accounts and ordered from a table I have on my database. I've made some complex queries and haven't been to solve my problem.

First thought was to group by ip and count(user_id) but users can have more than one order so these wll be counted as well (eg if I use the above, the ip "192.168.1.1" will return 3 and not 2 which I want

My data is like

| order_id  |  user_id  |     ip      |
---------------------------------------
| 1001      |   2       | 192.168.1.1 |    
| 1002      |   5       | 192.168.1.1 |    
| 1003      |   2       | 192.168.1.1 |    
| 1004      |   12      |   18.15.0.1 |    
| 1005      |   9       |    10.0.0.1 |

Result needed IP 192.168.1.1 cause it has 2 different user_id

Any help is appreciated

Upvotes: 0

Views: 88

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

You are looking for IPs given to more than one user? Then group by IP and count distinct users.

select ip
from mytable
group by ip
having count(distinct user_id) > 1;

EDIT: To get the users associated with that 

select user_id, ip
from mytable
where ip in
(
  select ip
  from mytable
  group by ip
  having count(distinct user_id) > 1
);

And here is the same with an EXISTS clause:

select user_id, ip
from mytable
where exists
(
  select *
  from mytable other
  where other.ip = mytable.ip
  and other.user_id <> mytable.user_id
);

Upvotes: 1

SoulTrain
SoulTrain

Reputation: 1904

use Distinct inside your Count

SELECT COUNT(Distinct user_ID)
FROM table
GROUP BY ip

Upvotes: 2

Joe Taras
Joe Taras

Reputation: 15379

Try this:

SELECT t.ip, t.user_id,
   (SELECT COUNT(1)
   FROM yourtable t3 WHERE t.user_id = t3.user_id
   AND t.ip = t3.ip) as total
FROM yourtable t
WHERE EXISTS(
    SELECT 'duplicate'
    FROM yourtable t2
    WHERE t.user_id = t2.user_id
    AND t.ip = t2.ip
    AND t.order_id < t2.order_id
)

Upvotes: 1

Related Questions