SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    getting into deeper queries - help plz.

    I am querying one table - statistics. To begin with, I want to find the total page views as well as unique visitors. Successful with page_views but not #unique visitors. Currently showing as 2 unique visitors when there are about 61000.

    Code MySQL:
    SELECT page_views.count AS total_pages
             , uniques.count AS unique_visitors
      FROM statistics AS stats
    INNER
      JOIN (
               SELECT count(*) AS count
                    , Session_ID 
               FROM statistics AS stats
           INNER
                JOIN business_details AS bd ON bd.web_url = stats.Domain_Viewed
                 AND bd.business_id =1
             ) AS page_views
              on page_views.Session_ID = stats.Session_ID
    INNER
      JOIN (
              SELECT count(*) AS count
                       , statistics.Session_ID
              FROM statistics AS statistics
           INNER
               JOIN business_details AS bd
                 ON bd.web_url = statistics.Domain_Viewed
                AND bd.business_id =1
                      group by statistics.Session_ID
            ) AS uniques
               ON uniques.Session_ID = stats.Session_ID

    Am I close? I am as much interested in making this work as in making it efficient and so far we are at 3.5 secs for this query


    bazz

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    looks to me like somebody forgot a GROUP BY clause somewhere
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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