simple SQL query problem...

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Chris Dangerfield (chris_at_dangerfieldbrothers.co.uk)
Date: 05/25/04 

Date: Tue, 25 May 2004 20:57:07 +0100

Hi there,

I could do with a little help on a sql query.

I have a table with 4 cols

resolution, browser, datetime, ipaddress

I am trying to establish how many unique visitors in a receiving and what
browser and resolution they are using.

CREATE VIEW dbo.view_www_visitor_stats
AS
SELECT TOP 100 PERCENT resolution, COUNT(resolution) AS [count]
FROM dbo.tbl_www_visitor_stats
GROUP BY resolution
ORDER BY COUNT(resolution) DESC

The query above produces the results I require, only when I need to add in
the requirement to ensure the query is being run on a unique ip address.
Sometimes my users are notching up more than one row in the database...
ie...

1024*768 Internet Explorer 25/05/2004 19:11:31
194.165.174.153
1024*768 Internet Explorer 25/05/2004 19:12:31
194.80.193.160
800*600 Internet Explorer 25/05/2004 19:12:33 195.92.67.75
800*600 Internet Explorer 25/05/2004 19:14:10 195.92.67.75
1024*768 Internet Explorer 25/05/2004 19:14:22 82.32.114.126
800*600 Internet Explorer 25/05/2004 19:14:26 195.92.67.75

Note the duplication of rows 3&4, from the same user.

My query gives me...

1024*768 3
800*600 3

What it should do is discount one of the 800*600, as it came from the same
ip address.

So I need this query to be run against distinct ip addresses, however, I
can't seem to get it to work with the distinct keyword or group by.

Can someone help ?

Best Regards 

--
Chris Dangerfield

Relevant Pages

  • Re: DNS is slow
    ... This is the wrong query because it will first query for ... DNS cache on the client before the second query? ... Do you want to say that "name resolution via Internet Explorer is ... Wireshark is the way to go. ...
    (microsoft.public.windows.server.dns)
  • Re: simple SQL query problem...
    ... SELECT TOP 100 PERCENT resolution, ... ORDER BY COUNT(DISTINCT ipaddress) DESC ... > I could do with a little help on a sql query. ... > can't seem to get it to work with the distinct keyword or group by. ...
    (microsoft.public.sqlserver.programming)
  • Re: Simple program cwitching thru all video-modes craps-out ...
    ... is no standard graphics-bios command for that. ... Use VESA mode query commands to determine modes and capabilities ... change when I select another resolution ...
    (comp.lang.asm.x86)
  • Re: Problem with Ranking
    ... Director of Text Mining and Database Strategy ... RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. ... I have a problem with the ranking in my query.. ... 00004 in Resolution Description ...
    (microsoft.public.inetserver.indexserver)
  • Re: simple SQL query problem...
    ... > SELECT TOP 100 PERCENT resolution, COUNT(DISTINCT ipaddress) AS ... >> I could do with a little help on a sql query. ... >> Chris Dangerfield ...
    (microsoft.public.sqlserver.programming)