CODEWITHSUNDEEP
mysqlsql
Manuel Simeonov
Manuel Simeonov

Reputation: 13

SQL GROUP BY two columns but grouping one by one

I have simple table that keeps all the data when users login.

id | user_id | ip | date

As you know I have users logging from many IPs and users logging from one IP. I want to get those that log from one IP. So I need to GROUP BY ip but also need the count of UNIQUE user_id that has data with this IP. And I want to order it by the most users logging from the same IP.

The problem is GROUP BY groups by both ip and user_id. I guess I need insite query, but I have no idea how to do it.

SELECT *, COUNT(`id`) 
FROM `TABLE` 
GROUP BY `ip`, `user_id` 
ORDER BY COUNT(`id`)

Any idea? :)

Upvotes: 1

Views: 98

Answers (2)

Daniel André
Daniel André

Reputation: 1258

I think this is what you are looking for:

SELECT user_id, COUNT(DISTINCT ip) as unique_ip_count
    FROM TABLE
    GROUP BY user_id
    HAVING COUNT(DISTINCT ip)=1

Upvotes: 0

AHiggins
AHiggins

Reputation: 7219

You're looking for the DISTINCT keyword: 

SELECT IP, COUNT(DISTINCT user_ID) AS NumberOfUsers
FROM TABLE
GROUP BY IP
HAVING COUNT(DISTINCT user_ID) > 1 -- this will filter to show only IPs with multiple users
ORDER BY NumberOfUsers DESC

Upvotes: 2

Related Questions