Getting into deeper queries - help plz

Databases
1

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.


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 :frowning:

bazz

2

looks to me like somebody forgot a GROUP BY clause somewhere :wink: