Fog Juice Posted July 14, 2011 Share Posted July 14, 2011 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 Quote Link to comment https://forums.phpfreaks.com/topic/241966-top-ten-query-duplicates/ Share on other sites More sharing options...
fenway Posted July 15, 2011 Share Posted July 15, 2011 You're missing a GROUP BY nickname. Quote Link to comment https://forums.phpfreaks.com/topic/241966-top-ten-query-duplicates/#findComment-1243030 Share on other sites More sharing options...
Fog Juice Posted July 15, 2011 Author Share Posted July 15, 2011 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. Quote Link to comment https://forums.phpfreaks.com/topic/241966-top-ten-query-duplicates/#findComment-1243277 Share on other sites More sharing options...
requinix Posted July 15, 2011 Share Posted July 15, 2011 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? Quote Link to comment https://forums.phpfreaks.com/topic/241966-top-ten-query-duplicates/#findComment-1243280 Share on other sites More sharing options...
Fog Juice Posted July 16, 2011 Author Share Posted July 16, 2011 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. Quote Link to comment https://forums.phpfreaks.com/topic/241966-top-ten-query-duplicates/#findComment-1243307 Share on other sites More sharing options...
Fog Juice Posted July 16, 2011 Author Share Posted July 16, 2011 *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. Quote Link to comment https://forums.phpfreaks.com/topic/241966-top-ten-query-duplicates/#findComment-1243353 Share on other sites More sharing options...
TeNDoLLA Posted July 16, 2011 Share Posted July 16, 2011 What output do you expect to get from the example data you have in your first post? Quote Link to comment https://forums.phpfreaks.com/topic/241966-top-ten-query-duplicates/#findComment-1243464 Share on other sites More sharing options...
fenway Posted July 16, 2011 Share Posted July 16, 2011 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. Quote Link to comment https://forums.phpfreaks.com/topic/241966-top-ten-query-duplicates/#findComment-1243489 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.