Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2003
    Posts
    43

    Unanswered: Combine two count queries

    I've got a table full of website traffic stats, one row for every time the page loads (see attached picture). The columns that matter in this case are "countdate" and "sessionid".

    I use the following query to count the number of hits to the page in a specific year and month, regardless of sessionid:
    Code:
    SELECT COUNT(*) AS hits
    FROM counterstats
    WHERE YEAR(countdate)=2004 And MONTH(countdate)=12
    And I use this query to count the number of "unique" visits to the page (if the sessionid of two rows is the same, it's the same visitor just reloading the page)
    Code:
    SELECT COUNT(*) AS uniquehits
    FROM (SELECT DISTINCT sessionid FROM counterstats WHERE YEAR(countdate) = 2004 AND MONTH(countdate) = 12)
    I would very much like to combine these two queries so I would get two columns, one for "hits", and one for unique visits.

    Thanks very much in advance for any assistance!
    Attached Thumbnails Attached Thumbnails count_table.gif  
    Last edited by Oddish; 12-23-04 at 06:48.

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I would use a union query:

    SELECT SUM(HITS) AS HITS, SUM(UNIQUEHITS) AS UNIQUEHITS
    FROM (
    SELECT COUNT(*) AS hits, 0 as uniquehits
    FROM counterstats
    WHERE YEAR(countdate)=2004 And MONTH(countdate)=12
    UNION
    SELECT 0 AS hits, COUNT(*) AS uniquehits
    FROM (SELECT DISTINCT sessionid FROM counterstats WHERE YEAR(countdate) = 2004 AND MONTH(countdate) = 12))
    Inspiration Through Fermentation

  3. #3
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    There are two options: union or cartesian product

    Union: You should combine your queries like:

    SELECT count(*) As Cnt, "hits" as dsc
    FROM counterstats
    WHERE YEAR(countdate)=2004 And MONTH(countdate)=12
    UNION
    SELECT COUNT(*) As Cnt, "uniquehits" As dsc
    FROM (SELECT DISTINCT sessionid FROM counterstats WHERE YEAR(countdate) = 2004 AND MONTH(countdate) = 12)

    I didn't test the cartesian product option, but the following should work as well:

    SELECT hits, uniquehits
    FROM (SELECT count(*) As hits
    FROM counterstats
    WHERE YEAR(countdate)=2004 And MONTH(countdate)=12) A
    ,
    (SELECT COUNT(*) As uniquehits
    FROM (SELECT DISTINCT sessionid FROM counterstats WHERE YEAR(countdate) = 2004 AND MONTH(countdate) = 12)) B
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  4. #4
    Join Date
    Mar 2003
    Posts
    43
    Thanks a lot for the help guys! I'll try it.

Posting Permissions

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