Jump to content

Problem using SUM() with LEFT JOIN


gunslinger008

Recommended Posts

I\'m using MySQL v4.0.13, and I have the following tables: PROJECTS, BUDGETS, EXTRA_COSTS. Each project can have multiple (or zero) budgets and multiple (or zero) extra costs. I\'m trying to get the budget and extra cost totals for a particular project using the following query:

 

SELECT SUM(budget_tot) AS budget_total, SUM(extrac_tot) AS extracost_total

FROM PROJECTS

LEFT JOIN  BUDGETS      ON budget_proj_id = proj_id

LEFT JOIN  EXTRA_COSTS  ON extrac_proj_id = proj_id

WHERE proj_id = 143

 

The problem is when a project has a more than one budget or more than one extra cost each amount is multiplied by the number of rows in the other table. For example, if a project has five budgets and two extra costs, each budget amount would be included twice and each extra cost amount would be included five times.

 

I realize what\'s happening here, but it\'s not what I expected (and obviously not what I\'d like to happen). I have worked out the following trick to get the right numbers, but it\'s a bit of a hack:

 

SELECT ( SUM(budget_tot) / IF(COUNT(DISTINCT extrac_id)=0, 1, COUNT(DISTINCT extrac_id)) ) AS budget_total,

      ( SUM(extrac_tot) / IF(COUNT(DISTINCT budget_id)=0, 1, COUNT(DISTINCT budget_id)) ) AS extracost_total

FROM PROJECTS

LEFT JOIN  BUDGETS      ON budget_proj_id = proj_id

LEFT JOIN  EXTRA_COSTS  ON extrac_proj_id = proj_id

WHERE proj_id = 143

 

Does anyone know of a better way to do this without resorting to separate queries? It would be nice to have something like SUM(DISTINCT ...). Does anyone know if the LEFT JOIN behavior has changed in MySQL v4.1?

 

Thanks,

Sean

Link to comment
Share on other sites

you can try this

 

SELECT SUM(budget_tot) AS budget_total, SUM(extrac_tot) AS extracost_total

FROM PROJECT, SBUDGETS, EXTRA_COSTS

WHERE extrac_proj_id = proj_id AND  

     budget_proj_id = proj_id AND

     proj_id = 143

Thanks for the suggestion, but unfortunately it doesn\'t work. :/ When a project has more than one budget or extra cost the same thing happens as I described. And, without the LEFT JOINs, if a project has zero budgets or extra costs then both sums become null.

 

I\'ve decided that what\'s needed is formal subqueries, so I\'ll just have go with separate queries until MySQL v4.1 is ready for production use. We migrated from Oracle a while ago, and while I can\'t test it, I believe Oracle does (left) outer joins in a subquery-like fashion.

 

Cheers,

Sean

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.