Count unique IP addresses - 500 POINTS

SELECT DISTINCT COUNT(ipaddress) as ipaddresscount FROM tblAnalytics

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
%>

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

Could you please post me data i.e. all records pls

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

Let me test out, will get back soon. What kind of database are you using ?

MS Access

Sorry forgot group by. TRy this

SQL = "SELECT DISTINCT COUNT(RemoteAddr) as ipaddresscount, RemoteAddr FROM tblAnalytics Group BY RemoteAddr"

Cannot include Memo, OLE, or Hyperlink Object when you select unique values (RemoteAddr).

I get that error when i run it now...

What's the datatype

it was memo and now i changed it to text... is that right?

Yes

You cannot use any aggregrate function with memo fields. Change it to text and let me know

DId it fixed your problem ?

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

<%
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
%>

Hi, This code still only returns the value 1?

Thanks

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

>>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

>>Then lose the sarcasm.<<
That wasnt sarcasm, that was a genuine complement, please lighten up.


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