SELECT SUM(budget_tot) AS budget_total, SUM(extrac_tot) AS extracost_totalFROM PROJECTSLEFT JOIN BUDGETS ON budget_proj_id = proj_idLEFT JOIN EXTRA_COSTS ON extrac_proj_id = proj_idWHERE 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_totalFROM PROJECTSLEFT JOIN BUDGETS ON budget_proj_id = proj_idLEFT JOIN EXTRA_COSTS ON extrac_proj_id = proj_idWHERE 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?