• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 359
  • Last Modified:

Count unique IP addresses - 500 POINTS

How would i go about counting the total distint IP addreses from a table column call ipaddress

I was thinking something like below:

<%
Set RS = Server.CreateObject("ADODB.Recordset")
SQL = "SELECT SUM DISTINCT(ipaddress) as ipaddresscount FROM tblAnalytics"
RS.Open SQL,Connection,3,3

Response.Write ipaddresscount

RS.Close
Set RS = Nothing
Connection.Close
Set Connection = Nothing
%>

What would be the exact syntax of this?

Thanks,
Carl
0
net-workx
Asked:
net-workx
  • 10
  • 9
  • 2
1 Solution
 
jitgangulyCommented:
SELECT DISTINCT COUNT(ipaddress) as ipaddresscount FROM tblAnalytics
0
 
jitgangulyCommented:
Try this

<%
Set RS = Server.CreateObject("ADODB.Recordset")
SQL = "SELECT DISTINCT COUNT(ipaddress) as ipaddresscount FROM tblAnalytics"
RS.Open SQL,Connection,3,3

Response.Write rs(0)

RS.Close
Set RS = Nothing
Connection.Close
Set Connection = Nothing
%>
0
 
net-workxAuthor Commented:
I have tried this.....

<%
Set RS = Server.CreateObject("ADODB.Recordset")
SQL = "SELECT DISTINCT COUNT(RemoteAddr) as ipaddresscount FROM tblAnalytics"
RS.Open SQL,Connection,3,3

Response.Write RS("ipaddresscount")

RS.Close
Set RS = Nothing
%>

AND THIS.....

<%
Set RS = Server.CreateObject("ADODB.Recordset")
SQL = "SELECT DISTINCT COUNT(RemoteAddr) as ipaddresscount FROM tblAnalytics"
RS.Open SQL,Connection,3,3

Response.Write rs(0)

RS.Close
Set RS = Nothing
%>

And they both return 15 which is the total amount of records.... the correct return should be two as there are only two unique ip addresses in there....

Any Ideas,
Carl
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
jitgangulyCommented:
Could you please post me data i.e. all records pls
0
 
net-workxAuthor Commented:
Ok...

RemoteHost
88.109.65.96
88.109.65.96
88.109.65.96
88.109.65.96
88.109.65.96
88.109.65.96
88.109.65.96
89.0.231.20
89.0.231.20
89.0.231.20
89.0.231.20
89.0.231.20
89.0.231.20
89.0.231.20
89.0.231.20
89.0.231.20

Is this what you wanted?

Thanks,
Carl
0
 
jitgangulyCommented:
Let me test out, will get back soon. What kind of database are you using ?
0
 
net-workxAuthor Commented:
MS Access
0
 
jitgangulyCommented:
Sorry forgot group by. TRy this

SQL = "SELECT DISTINCT COUNT(RemoteAddr) as ipaddresscount, RemoteAddr FROM tblAnalytics Group BY RemoteAddr"
0
 
net-workxAuthor Commented:
Cannot include Memo, OLE, or Hyperlink Object when you select unique values (RemoteAddr).

I get that error when i run it now...
0
 
jitgangulyCommented:
What's the datatype
0
 
net-workxAuthor Commented:
it was memo and now i changed it to text... is that right?
0
 
jitgangulyCommented:
Yes
0
 
jitgangulyCommented:
You cannot use any aggregrate function with memo fields. Change it to text and let me know
0
 
jitgangulyCommented:
DId it fixed your problem ?
0
 
net-workxAuthor Commented:
HI i now have...

<%
Set RS = Server.CreateObject("ADODB.Recordset")
SQL = "SELECT DISTINCT COUNT(RemoteAddr) as ipaddresscount, RemoteAddr FROM tblAnalytics Group BY RemoteAddr"
'SQL = "SELECT DISTINCT COUNT(RemoteAddr) as ipaddresscount FROM tblAnalytics"
RS.Open SQL,Connection,3,3

Response.Write RS(0)

RS.Close
Set RS = Nothing
%>

And it returns 1- i have just counted manually 4 unique IP Addresses in the db table.

Thanks,
Carl
0
 
jitgangulyCommented:
<%
Set RS = Server.CreateObject("ADODB.Recordset")
SQL = "SELECT DISTINCT COUNT(RemoteAddr) as ipaddresscount, RemoteAddr FROM tblAnalytics Group BY RemoteAddr"
set RS = Connection.Execute(SQL)

Response.Write RS(0)

RS.Close
Set RS = Nothing
%>
0
 
net-workxAuthor Commented:
Hi, This code still only returns the value 1?

Thanks
0
 
Anthony PerkinsCommented:
So that we can go ahead and close this abandoned question, let me explain to you what is wrong with the approach you are taking.  

First there is no point whatsoever (even I suspect in MS Access) to use DISTINCT and the GROUP BY.  They both have there distinct functionality and there is no reason to combine them.

Secondly, it seems you need a count of unique RemoteAddr not a list of RemoteAddr.  In other words you do NOT need a list like this:

123.123.123.101
123.123.123.102
123.123.123.103
123.123.123.104
123.123.123.105
123.123.123.106
123.123.123.107
123.123.123.108
123.123.123.109

But rather just a simple count.  In this case 9.  

Incidentally, the reason the "code still only returns the value 1" is because the first RemoteAddr possible only appears once (Count = 1)

So to the solution and a small digression.  If you were using MS SQL Server the solution would be quite simple:
Select COUNT(Distinct RemoteAddr) as ipaddresscount From tblAnalytics

Unfortunately you are not, so it becomes a tad more complicated with MS Access:
Select COUNT(*) As ipaddresscount From (SELECT RemoteAddr FROM tblAnalytics Group By RemoteAddr)

This (using derived tables) does return the correct results with the new versions of MS Access, but I suspect you may find that it is not supported in MS Access 97.  However, if you are using JET 4.0 (as I suspect you are) you should be fine.

I tested it with the Orders table in the Northwind database in both MS SQL Server and MS Access as follows:
In MS SQL Server: Select COUNT(Distinct CustomerID) As CustomerCount From Orders

In MS SQL Server and MS Access:
Select COUNT(*) As CustomerCount From (SELECT CustomerID FROM Orders Group By CustomerID) A

(in MS Access the "A" alias is not required and can be left off)


I trust this answer now fully answers the question and you can now close this thread.  Otherwise, please post a message in Community Support to close it.
0
 
net-workxAuthor Commented:
I bow down to your superior knowledge!

Right my script now says:

<%
Set RS = Server.CreateObject("ADODB.Recordset")
SQL = "Select COUNT(*) As ipaddresscount From (SELECT RemoteAddr FROM tblAnalytics Group By RemoteAddr)"
set RS = Connection.Execute(SQL)

Response.Write ipaddresscount

RS.Close
Set RS = Nothing
%>

But nothing is displayed?  I am assuming the response.write bit is incorrect?

Thanks.
Carl
0
 
Anthony PerkinsCommented:
>>I bow down to your superior knowledge!<<
Then lose the sarcasm.

>> I am assuming the response.write bit is incorrect?<<
Yes, it is incorrect.  It should be:
Response.Write RS.Fields("ipaddresscount") Value
0
 
net-workxAuthor Commented:
>>Then lose the sarcasm.<<
That wasnt sarcasm, that was a genuine complement, please lighten up.


>>Response.Write RS.Fields("ipaddresscount") <<
This now works. Thankyou.

0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 10
  • 9
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now