Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2007
    Posts
    212

    Unanswered: SQL help on aggregates

    Hi,

    Am using the folling query to give me a list of jobs, the number of applications for those jobs and the number of times the advert has been viewed.

    Code:
    SELECT jobs.job_id
             , jobs.job_title
             , COUNT(apps.app_id) AS applicantions
             , COUNT(log.job_view_id) AS job_views
    FROM tbl_jobs AS jobs 
    LEFT JOIN tbl_applications AS apps ON jobs.job_id = apps.job_id 
    LEFT JOIN tbl_job_view_log AS log ON jobs.job_id = log.job_id 
    GROUP BY jobs.job_id
    However I am getting incorrect results. If there is 1 application and the advert has been viewed twice am getting a count of 2 for applications as well.

    Can some one point me in the right direction.

    Cheers.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you're seeing cross join effects, when multiple joined rows are matched with multiple joined rows

    instead of joining and joining and then grouping, you should be grouping and grouping and then joining, if you know what i mean
    Code:
    SELECT jobs.job_id
         , jobs.job_title
         , subquery_a.applications
         , subquery_v.job_views
      FROM tbl_jobs AS jobs 
    LEFT OUTER
      JOIN ( SELECT job_id
                  , COUNT(*) AS applications
               FROM tbl_applications 
             GROUP
                 BY job_id
           ) AS subquery_a 
        ON subquery_a.job_id = jobs.job_id 
    LEFT OUTER
      JOIN ( SELECT job_id
                  , COUNT(*) AS job_views
               FROM tbl_job_view_log 
             GROUP
                 BY job_id
           ) AS subquery_v
        ON subquery_v.job_id = jobs.job_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2007
    Posts
    212
    you're seeing cross join effects, when multiple joined rows are matched with multiple joined rows

    instead of joining and joining and then grouping, you should be grouping and grouping and then joining, if you know what i mean

    Many thanks.

Posting Permissions

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