0

I am trying to do a weird SQL query that I can't seem to figure out. I have the following schema:

username ip
user1 12345
user2 12345
user3 11111

I am trying to count the number of user's per ip. So for instance it would report to me

11111 1
12345 2

I want to also sort this by the least to the most, so that I can see ip 12345 has 2 accounts associated with it and 11111 has 1 account associated with it.

Thanks!

MasterGberry
  • 2,800
  • 6
  • 37
  • 56
  • possible duplicate of [How to use count and group by at the same select statement](http://stackoverflow.com/questions/2722408/how-to-use-count-and-group-by-at-the-same-select-statement) – Marc B Mar 06 '14 at 01:53
  • 2
    if you think a simple counting query is weird, you're in for some major shocks down the road... – Marc B Mar 06 '14 at 01:53
  • @MarcB thanks i was overthinking this. – MasterGberry Mar 06 '14 at 01:55

2 Answers2

3

Group by the ip and add distinct to your user count to count only unique names

select ip, 
       count(distinct username) as unique_user_count
from your_table
group by ip
juergen d
  • 201,996
  • 37
  • 293
  • 362
0

Here is an SQL Fiddle exemplifying what you need.

SELECT ip, COUNT(username) AS username_count
FROM `sample`
GROUP BY ip

GROUP BY allows you to summarize the data by ip. The DISTINCT shouldn't be necessary if the username column has the UNIQUE constraint defined on it.

Derek W
  • 9,708
  • 5
  • 58
  • 67