SQL help on aggregates

ozzii · #1 (**permalink**) 05-27-11, 16:04

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.

r937 · #2 (**permalink**) 05-27-11, 17:29

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

ozzii · #3 (**permalink**) 05-29-11, 09:24

Quote:

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech