这应该起作用：

select t2.name, sum(t2.cost), coalesce(sum(t1.amount), 0) as amount
from (
   select id, name, sum(cost) as cost
   from `Cost`
   group by id, name
) t2
left join (
   select costID, sum(amount) as amount
   from `Payment`
   group by CostID
) t1 on t2.id = t1.costID
group by t2.name

sqlfiddle公司

您需要在单独的查询中进行计算，然后将它们连接在一起。
第一个是直截了当的。
第二个你需要得到 name 与基于 cost_id sql fiddle演示

SELECT C.`name`, C.`sum_cost`, COALESCE(P.`sum_amount`,0 ) as `sum_amount`
FROM (
    SELECT `name`, SUM(`cost`) as `sum_cost`
    FROM `Cost`
    GROUP BY `name`
    ) C
LEFT JOIN (    
    SELECT `Cost`.`name`, SUM(`Payment`.`amount`) as `sum_amount`
    FROM `Payment`
    JOIN `Cost` 
       ON `Payment`.`costID` = `Cost`.`id`
    GROUP BY `Cost`.`name`
  ) P
  ON C.`name` =  P.`name`

输出

| name | sum_cost | sum_amount |
|------|----------|------------|
|    A |      300 |         80 |
|    B |      200 |         30 |

有几个问题。首先，列引用应该是限定的，而不是聚合函数。
这是无效的：

table_alias.SUM(column_name)

应该是：

SUM(table_alias.column_name)

此查询应返回要查找的前两列：

SELECT c.name         AS `name`
     , SUM(c.cost)    AS `sum(cost)`
  FROM `Cost Table` c
 GROUP BY c.name
 ORDER BY c.name

当您将联接引入另一个表时，例如 Product Table ，在哪里 costid 不是唯一的，你有可能产生（部分）笛卡尔积。
若要查看它的外观，若要查看发生了什么，请移除 GROUP BY 以及骨料 SUM() 函数，并查看使用join操作的查询返回的详细信息行。

SELECT c.id               AS `c.id`
       , c.cost             AS `c.cost`
       , c.name             AS `c.name`
       , p.pid              AS `p.pid`
       , p.amount           AS `p.amount`
       , p.costid           AS `p.costid`
    FROM `Cost Table` c
    LEFT
    JOIN `Payment Table` p
      ON p.costid = c.id
   ORDER BY c.id, p.pid

这将得到回报：

c.id | c.cost |  c.name | p.pid | p.amount | p.costid
 1    |    100 |  A      | 1     |       10 | 1       
 1    |    100 |  A      | 2     |       20 | 1       
 1    |    100 |  A      | 4     |       50 | 1       
 2    |    200 |  B      | 3     |       30 | 2
 3    |    200 |  A      | NULL  |     NULL | NULL

请注意，我们从中得到了id=1行的三个副本 Cost Table .
所以，如果我们修改了查询，添加 GROUP BY c.name ，并将c.cost Package 在sum（）聚合中，我们将得到total的膨胀值 cost .
为了避免这种情况，我们可以将 amount 从 Payment Table ，所以我们每行只能得到一行 costid . 然后，当我们执行连接操作时，我们将不会从中生成行的重复副本 Cost .
下面是一个查询，用于聚合 Payment Table ，所以我们为每个 costid .

SELECT p.costid
       , SUM(p.amount) AS tot_amount
    FROM `Payment Table` p 
   GROUP BY p.costid
   ORDER BY p.costid

这将返回：

costid | tot_amount
  1      | 80
  2      | 30

我们可以像使用表一样使用该查询的结果，方法是将该查询设置为“内联视图”。在本例中，我们为 v 查询结果(在mysql术语中，“内联视图”称为“派生表”。）

SELECT c.name                      AS `name`
     , SUM(c.cost)                 AS `sum_cost`
     , IFNULL(SUM(v.tot_amount),0) AS `sum_amount`
  FROM `Cost Table` c
  LEFT
  JOIN ( -- inline view to return total amount by costid 
         SELECT p.costid
              , SUM(p.amount) AS tot_amount
           FROM `Payment Table` p
          GROUP BY p.costid
          ORDER BY p.costid
       ) v
    ON v.costid = c.id
 GROUP BY c.name
 ORDER BY c.name