Results 1 to 14 of 14
  1. #1
    Join Date
    Feb 2002
    Posts
    23

    Unanswered: Web logging Query with count / group function

    I am fluent in ASP, but this one is throwing me for quite a loop.

    I have a 500k record web log that obiously indicates the target and logtime among other uselful info.

    I need to create a query that tells me every unique page that was viewed each day with a count of how many times that unique page was viewed.

    The output should look like this:

    Date Target Viewed
    12-Jul-03 /images/photo1.jpg 5
    12-Jul-03 /images/photo2.jpg 7
    12-Jul-03 /pages/Amy.htm 23
    12-Jul-03 /pages/John.htm 56
    11-Jul-03 /images/photo1.jpg 3
    11-Jul-03 /images/photo2.jpg 6
    11-Jul-03 /pages/Amy.htm 76
    11-Jul-03 /pages/John.htm 23
    10-Jul-03 /images/photo1.jpg 5
    10-Jul-03 /images/photo2.jpg 8
    10-Jul-03 /pages/Amy.htm 44
    10-Jul-03 /pages/John.htm 78

    The Date should pull values from the logtime field and lose the time part so as to group by Date. Target is the value in the IIS log target field. Viewed is the count of how many times each unique target appears in the recordset for each day.

    Later I will add a where clause so I can key on and get history for individual pages. After that, the data will be charted and graphed.

    This seemed simple enough, but I've tried everyhting I know how to do, and I can't get any of it to work.

    Please help you ASP / SQL GURUS!
    Last edited by PaulGQ; 09-03-03 at 06:38.

  2. #2
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: Web logging Query with count / group function

    Hi Paul,

    Assume, your table T has two fields: the log timestamp ts, and the page p. Your query should be something like:

    SELECT DateAdd(dd, DateDiff(dd, '1900-01-01', ts), '1900-01-01'), p, count(*)
    FROM T
    GROUP BY DateAdd(dd, DateDiff(dd, '1900-01-01', ts), '1900-01-01'), p


    Cheers!
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  3. #3
    Join Date
    Feb 2002
    Posts
    23
    When I plug in the proper field names, Access prompts me with a dialogue box to plug in a value for "dd".

  4. #4
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by PaulGQ
    When I plug in the proper field names, Access prompts me with a dialogue box to plug in a value for "dd".
    For Access use something like this:

    SELECT Format([ts],'dd-mm-yyyy') AS 'dd/mm/yyyy',p,count(p)
    FROM T
    GROUP BY Format([ts],'dd-mm-yyyy'),p;

  5. #5
    Join Date
    Feb 2002
    Posts
    23
    Originally posted by snail
    For Access use something like this:

    SELECT Format([ts],'dd-mm-yyyy') AS 'dd/mm/yyyy',p,count(p)
    FROM T
    GROUP BY Format([ts],'dd-mm-yyyy'),p;
    So far, that works VERY well, but the date comes displays like text that when sorted does not display chronologically, it sorts alphabetically.

    I swapped dd-mm-yyy for mm-dd-yyyy which helped a little bit, but displays as

    'mm-dd-yyyy' Target Hits
    02-16-2003 /images/photo1.jpg 17
    02-16-2003 /images/photo2.jpg 22
    02-16-2002 /images/photo1.jpg 32
    02-16-2002 /images/photo2.jpg 67
    02-15-2003 /images/photo1.jpg 14
    02-15-2003 /images/photo2.jpg 22
    02-15-2002 /images/photo1.jpg 95
    02-15-2002 /images/photo2.jpg 9

    When I try to format the expression in design view, formatting as "date" does not appear, in fact nothig is in the list.

    WE ARE SO CLOSE, YOU DA MAN!

  6. #6
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    SELECT Format([ts],'dd-mm-yyyy') AS 'dd/mm/yyyy',p,count(p)
    FROM T
    GROUP BY Format([ts],'dd-mm-yyyy'),p;
    order by Format(Format([ts],'dd-mm-yyyy'),'#');

  7. #7
    Join Date
    Feb 2002
    Posts
    23
    Originally posted by snail
    SELECT Format([ts],'dd-mm-yyyy') AS 'dd/mm/yyyy',p,count(p)
    FROM T
    GROUP BY Format([ts],'dd-mm-yyyy'),p;
    order by Format(Format([ts],'dd-mm-yyyy'),'#');
    This gave the same results.

  8. #8
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Try this one:

    SELECT Format([ts],'mm/dd/yyyy') AS ['mm/dd/yyyy'], T.p, Count(T.p) AS CountOfp, CVDate(Format([ts],'mm/dd/yyyy')) AS Expr1
    FROM T
    GROUP BY Format([ts],'mm/dd/yyyy'), T.p
    ORDER BY CVDate(Format([ts],'mm/dd/yyyy'));

    Also, check date settings on your PC DD/MM or MM/DD....

  9. #9
    Join Date
    Feb 2002
    Posts
    23
    Originally posted by snail
    Try this one:

    SELECT Format([ts],'mm/dd/yyyy') AS ['mm/dd/yyyy'], T.p, Count(T.p) AS CountOfp, CVDate(Format([ts],'mm/dd/yyyy')) AS Expr1
    FROM T
    GROUP BY Format([ts],'mm/dd/yyyy'), T.p
    ORDER BY CVDate(Format([ts],'mm/dd/yyyy'));

    Also, check date settings on your PC DD/MM or MM/DD....
    I will check local setting, but when not performing this complicated query, the date sorting works fine.

  10. #10
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    To order your datetime field correctly, use

    select p, format(ts, "yyyy-mm-dd"), count(*)
    from T
    group by p, format(ts, "yyyy-mm-dd")
    order by p, format(ts, "yyyy-mm-dd")

    P.S.: Why you are asking a MS Access question in the SQL Server forum?
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  11. #11
    Join Date
    Feb 2002
    Posts
    23
    they don't have an access forum, and lets face it, if you know SQL server, you probably know access. I prefer to get my answers from the best. The help Ive been getting here has been tremendous.

  12. #12
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Paul, look better; there is an Access forum.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    In TSQL (also for an Access ADP Project):
    SELECT cast(CONVERT(varchar(10), LogTime, 120) as Datetime) as LogDate,
    WebPage,
    count(*) as Hits
    from Yourtable
    Group by cast(CONVERT(varchar(10), LogTime, 120) as Datetime)
    order by cast(CONVERT(varchar(10), LogTime, 120) as Datetime),
    WebPage

    Note: it may not be necessary to use cast in the group clause.


    In Access SQL:
    SELECT Int(LogTime) AS LogDate,
    WebPage,
    Count(LogTime) AS Hits
    FROM YourTable
    GROUP BY Int(LogTime), WebPage
    ORDER BY Int(LogTime), WebPage;

    In access, you can format your integer LogDate value as a date (m/d/yyy, or anything else). Access datetime and numeric values are implicitly converted.

    blindman

  14. #14
    Join Date
    Feb 2002
    Posts
    23
    I want to thank everyone espesically snail and doktoblue. Its working well - you guys are the best.

    Let us munch on this until we develop it further.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •