top ten query duplicates

[Fog Juice]

What I'm trying to do is create a top list which will display the top 10 usernames by unique ip address. So basically, if an ip address is showing multiple times with the same nick name, then only one entry should be counted.

 

This sql I'm using is below, but I'm unsure of how to filter it so it isn't showing every single time a nickname is entered by the same ip in the count. Only one nickname per IP should be counted in the sql statement below.

SELECT nickname, count(nickname) nbr FROM `log` group by ip_addr, nickname order by nbr desc limit 10;

 

id, nickname, ip_addr
1  fogjuice 2.2.2.210
2  fogjuice 2.2.2.210
3  fogjuice 21.6.3.210
4  bull  2.2.2.210
5  bull  29.20.13.2
6  bull  2.2.2.210
7  harvey  2.2.2.210
8  harvey  2.2.2.210
9  harvey  2.2.2.210
10  jane  2.2.2.210
11  tom   2.2.2.210

[fenway]

You're missing a GROUP BY nickname.

[Fog Juice]

You're missing a GROUP BY nickname.

 

thanks for trying, but nickname is already in the group by.

 

I'm trying to get it so it excludes all duplicate ip/nickname's in the count(nickname). So basically, if there are two or more rows with the same nickname and ip address, only one is counted.

[requinix]

That's exactly what it's doing: one row per nickname per IP address, with counts of both together.

What are you getting and what are you trying to get?

[Fog Juice]

That's exactly what it's doing: one row per nickname per IP address, with counts of both together.

What are you getting and what are you trying to get?

 

Thanks, however, I don't want it to show the total count, just the count of nickname per ip address.

 

For example, the print out in my original post shows fogjuice three times, using group by will obviously group them all together and print a count of 3 but that is incorrect for what i'm trying to do. It should only count two because there are only two unique ip addresses, so the third entry shouldn't be counted because it is a duplicate nickname/ip. I don't think what i'm trying to do is as simple as a group by, I'm hoping it isn't too hard, I just dont know where to begin.

[Fog Juice]

*update* basically I'm trying to do something like count all nicknames with a distinct ip address per nickname. So if a nickname has duplicate ip addresses, only the 1 ip address is counted.

[TeNDoLLA]

What output do you expect to get from the example data you have in your first post?

[fenway]

You're missing a GROUP BY nickname.

 

thanks for trying, but nickname is already in the group by.

 

I'm trying to get it so it excludes all duplicate ip/nickname's in the count(nickname). So basically, if there are two or more rows with the same nickname and ip address, only one is counted.

Sorry, my bad.

 

You'll need to do this in 2 parts -- first, get ALL usernames for unique IPs -- then filter out the top 10.