Re: simple SQL query problem...

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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

Date: Tue, 25 May 2004 21:11:51 +0100

That is not likely and these figures are to give me a guide as to
development for 800x600.

Thanks very much for your help

Chris

"Adam Machanic" <amachanic@hotmail._removetoemail_.com> wrote in message
news:#MxvRMpQEHA.3220@TK2MSFTNGP12.phx.gbl...
> Does this work for you:
>
>
> CREATE VIEW dbo.view_www_visitor_stats
> AS
> SELECT TOP 100 PERCENT resolution, COUNT(DISTINCT ipaddress) AS
[count]
> FROM dbo.tbl_www_visitor_stats
> GROUP BY resolution
> ORDER BY COUNT(DISTINCT ipaddress) DESC
>
>
> ... This will still not quite work properly if an IP comes in with more
than
> one resolution. What do you want to do in that case?
>
>
> "Chris Dangerfield" <chris@dangerfieldbrothers.co.uk> wrote in message
> news:oyNsc.2037$Dm2.172@front-1.news.blueyonder.co.uk...
> > 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: 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)
  • TBC - In the NY Times Today
    ... sponsored a resolution congratulating Bruce Springsteen on ... In the recent past, Senator Bill Frist, the Senate majority leader, has ... artists and athletes like Chris ... I am from New Jersey and a big Bruce ...
    (rec.music.artists.springsteen)
  • 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: Tomorrows Monster Telescopes
    ... Chris L Peterson wrote: ... resolution, of course. ... You're thinking in the limits of current technology. ...
    (sci.astro.amateur)
  • Re: Why does Xorg choose this resolution?
    ... Chris Green ... that line is the system resolution used. ... Identifier "Configured Video Device" ... Identifier "Configured Monitor" ...
    (Ubuntu)