homepage Welcome to WebmasterWorld Guest from 54.242.140.11
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
need help with a query to count distinct items
using mssql server
GaryK

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 4230869 posted 6:06 am on Nov 16, 2010 (gmt 0)

My query skills are sorely lacking so hopefully someone can help me with this.

My table is called IPAddresses and it contains:

DateAdded as datetime
IPAddress as varchar(15)

I want to know what each distinct IPAddress is, and I also want to know what the total count for each distinct IPAddress is. I can easily do either one, but can't figure out a way to do both so that I have both the count and the actual IPAddress being counted.

My purpose is to see which IP Addresses are hitting a site of mine the most, so sorting in descending order of the total count would be helpful.

This is a query I'll be executing manually once a day or so, so I'm not too concerned with how efficient it is.

Thanks.

 

DWarp9

5+ Year Member



 
Msg#: 4230869 posted 8:49 am on Nov 16, 2010 (gmt 0)

Hi Gary,

This should do the trick:
SELECT Count(*) as hits, IPAddress From IPAddresses Group By IPAddress Order by hits desc;


Hope this help.
-Peter

GaryK

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 4230869 posted 6:05 pm on Nov 16, 2010 (gmt 0)

That did the trick. Thanks very much, Peter. :)

syber

10+ Year Member



 
Msg#: 4230869 posted 4:16 pm on Nov 22, 2010 (gmt 0)

Even better would be:

SELECT Count(DISTINCT IPAddress), IPAddress
From IPAddresses
Order by hits desc

GaryK

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 4230869 posted 12:02 am on Nov 23, 2010 (gmt 0)

Aren't you missing "as hits" and a group by clause? Also, won't your Count(DISTINCT IPAddress) always return a value of 1?

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved