How does sum work with two JOINs?

Question

MySQL

such a query...
you need to calculate the amount of rate with different types from the same table

SELECT z.id,
count(r1.id) acount, sum(r1.rate) asum,
count(r2.id) ccount, sum(r2.rate) csum
FROM (zav z)
LEFT JOIN rating r1 ON z.id = r1.id AND r1.type = 'a'
LEFT JOIN rating r2 ON z.id = r2.id AND r2.type = 'c'
WHERE z.city =  'spb' AND z.closedzav =  '0'
GROUP BY z.id

with one join, everything works as it should, but with two joins, count and sum sum up all the values, and not separately r1 and r2, what to do?

Answer 1

GROUP BY (IF(r1.type = 'a', 'a', 'c')), z.id

Answer 2

Делайте JOIN с готовыми выборками из таблиц

SELECT `z`.`id`,
       `ra`.`count` AS `acount`, `ra`.`rate` AS `asum`,
       `rc`.`count` AS `ccount`, `rc`.`rate` AS `csum`
  FROM `zav` AS `z`
  LEFT JOIN (
    SELECT COUNT(*) AS `count`, sum(`rate`) AS `rate`
      FROM `rating` 
      WHERE `type` = 'a'
      GROUP BY `id`
  ) AS `ra` ON `ra`.`id` = `z`.`id`
  LEFT JOIN (
    SELECT COUNT(*) AS `count`, sum(`rate`) AS `rate`
      FROM `rating` 
      WHERE `type` = 'c'
      GROUP BY `id`
  ) AS `rc` ON `rc`.`id` = `z`.`id`
  WHERE `z`.`city` =  'spb' AND `z`.`closedzav` =  '0'