sql join and sum 3-CodePudding

I have problem with join and sum column. My query is

SELECT 
sum(IFNULL(`worker_hours`.`godziny`, 0)) as godziny,
sum(IFNULL(`worker_hours`.`wartosc`, 0)) as wartosc,
sum(IFNULL(`worker_cashes`.`kwota`, 0)) as kwota,
`workers`.*
FROM 
`workers`
LEFT join `worker_hours` on `worker_hours`.`pracownik` = `workers`.`id`
LEFT join `worker_cashes` on `worker_cashes`.`pracownik` = `workers`.`id`
WHERE `workers`.`id_user` = '3'
group by
  `workers`.`id`

The result sum *2 my query, what i do wrong? in i have this query is fine:

SELECT 
sum(IFNULL(`worker_hours`.`godziny`, 0)) as godziny,
sum(IFNULL(`worker_hours`.`wartosc`, 0)) as wartosc,
`workers`.*
FROM 
`workers`
left join `worker_hours` on `worker_hours`.`pracownik` = `workers`.`id`
WHERE `workers`.`id_user` = '3'
group by
  `workers`.`id`

the problem is the second left join

CodePudding user response：

You should aggregate first in the tables worker_hours and worker_cashes and then join workers to the resultsets of the aggregations so that you don't get the same row multiple times due to the multiple joins:

SELECT w.*,
       COALESCE(h.godziny, 0) AS godziny,
       COALESCE(h.wartosc, 0) AS wartosc,
       COALESCE(c.kwota, 0) AS kwota
FROM workers AS w
LEFT JOIN (
  SELECT pracownik,
         SUM(godziny) AS godziny,
         SUM(wartosc) AS wartosc
  FROM worker_hours 
  GROUP BY pracownik
) AS h ON h.pracownik = w.id
LEFT JOIN (
  SELECT pracownik,
         SUM(kwota) AS kwota
  FROM worker_cashes  
  GROUP BY pracownik
) AS c ON c.pracownik = w.id
WHERE w.id_user = '3';