Post reply

Subquery Totals

  • July 17, 2007 at 10:54 am

    #95322

    Hi,

    I use the following query in my stats page to find the unique visitors and the pages they visited per day in a given month.

    SELECT

    CONVERT(CHAR(10),LogDate,103) As Date_,

    Count(DISTINCT LogRemote_Addr) As Visitors,

    Count(Lid) As Pages

    FROM Log

    WHERE LogMonth=7

    Group by CONVERT(CHAR(10),LogDate,103)

    ORDER BY CONVERT(CHAR(10),LogDate,103)

    I would like to calculate the totals (Sum) of the "Visitors" and "Pages" also for the given month.

    I think I have to use a subquery to accomplish that but I can't figure it out. I would appreciate your help.

    Below is an example output to be more clear.

    I have the following fileds in a table.

    LogDate = date of visit

    LogRemoteAddr = ip of the visitor

    Lid = a common id (autonumber)

    The example

    Lid LogDate LogRemoteAdd

    1 15/7/2007 85.82.141.1

    2 15/7/2007 85.82.141.1

    3 15/7/2007 85.21.140.58

    4 16/7/2007 85.21.140.58

    5 16/7/2007 193.54.147.1

    6 17/7/2007 193.57.100.1

    This set according to my query will produce.

    Date Visitors Pages

    15/7/2007 2 3

    16/7/2007 2 2

    17/7/2007 1 1

    I would like to take the Sum of Visitors and Pages which is to 5 and 6 correlatively.

    Thanks for your help.

  • July 17, 2007 at 12:04 pm

    #720121

    Maybe the answer is a creation of a temporary table with the fields "Visitors", "Pages" and then call it from a simple Select statement with SUM.

    Any help.??

  • July 17, 2007 at 5:30 pm

    #720225

    It wouldn't be a "sum"... it would still be a COUNT except it wouldn't be DISTINCT.  And, because you are not counting DISTINCT LID's, you already have the sum of pages.  So, try this... (of course, I have no idea what the Lid column contains 'cuz you didn't post that in your example, so this might not be right)

     SELECT CONVERT(CHAR(10),LogDate,103) As Date_,

            COUNT(DISTINCT LogRemote_Addr) As Visitors,

            COUNT(DISTINCT Lid) As Pages

            COUNT(LogRemote_Addr) As TotalVisitors,

            COUNT(Lid) As TotalPages

       FROM Log

      WHERE LogMonth = 7

      GROUP BY CONVERT(CHAR(10),LogDate,103)

      ORDER BY CONVERT(CHAR(10),LogDate,103)

    Also (just curious), what are you going to do with LogMonth = 7 when you have more than 1 year of data stored in the table?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • July 18, 2007 at 2:08 am

    #720296

    Hi,

    Lid is the recodrs of the database.

    I have the sum of pages BUT only by grouping the dates.

    I want to have the monthly totals which can be the Sum of the visitors and pages counts.

    I hope you understand.

    I believe that first I have to create a temp table to store the Visitors and Pages per day and then with a simple Sum function I can count the totals. What u think?

    Thanks.

  • July 18, 2007 at 12:37 pm

    #720496

    Is this what you need

     

    select logmonth,logyear,count(logremote_addr) as visitors,sum(pages) as sumpages from

    (select month(logdate) as logmonth, year(logdate) as logyear,logremote_addr,count(lid) as pages from log

    group by

    month(logdate), year(logdate) ,logremote_addr) pagespervisitor

    group by logmonth,logyear

    order by logyear,logmonth

     

    results:

    7 2007 4 6

  • July 18, 2007 at 2:54 pm

    #720552

    Just make the existing query into a derived table and take the sums from that:

    select sum(Visitors) as VisitorCount, sum(Pages) as PageCount 
from (
    SELECT CONVERT(CHAR(10),LogDate,103) As Date_,
        Count(DISTINCT LogRemote_Addr) As Visitors,
        Count(Lid) As Pages
    FROM Log
    WHERE LogMonth = 7
) as Der
Group by Date_
ORDER BY Date_

    Tomm Carr
    --
    Version Normal Form -- http://groups.google.com/group/vrdbms

  • July 18, 2007 at 3:11 pm

    #720558

    Tomm,

    when I ran yours it didn't work because there was no group by in the derived table and when I added a group by on the date it returned the same results as the original query.

     

    In mine I failed to notice that the guy who visited on seperate days should be counted twice.

    Here is the fix

    select logmonth,logyear,count(logremote_addr) as visitors,sum(pages) as sumpages from

    (select day(logdate) as logday,month(logdate) as logmonth, year(logdate) as logyear,logremote_addr,count(lid) as pages from log

    group by

     day(logdate),month(logdate), year(logdate) ,logremote_addr) pagespervisitor

    group by logmonth,logyear

    order by logyear,logmonth

     

    results

    logmonth logyear vistors sumpages

    7 2007 5 6

     

     

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply