Как работает sum с двумя JOIN?

Question

[Денис Денис]

Нужно посчитать сумму rate с разными type из одной таблицы.

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

С одним джойном все работает как надо, но с двумя джойнами count и sum суммируют все значения, а не отдельно r1 и r2, как быть?

Answer 1

[Arman]

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

Answer 2

[Rsa97]

Делайте 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'