如何在左连接后对"case when"子句求和而不重复

Question

如何在CASE WHEN子句中获得金额的总和？

真实表格：

年份2022<2022><2022>//tr><2022>//tr><10>

id	名称	目标
10	罗纳尔多	5
10	罗纳尔多	5
11	mesi	5
11	mesi	5
10	罗纳尔多	2021
11	mesi	10	2021

select
id,
name,
sum(real_goal_now) as real_goal_now,
sum(real_goal_then) as real_goal_then,
sum(target_goal_now) as target_goal_now,
sum(target_goal_then) as target_goal_then
from
(
select
a.id,
a.name,
case
when a.year = year(curdate()) then a.goal else 0 end as real_goal_now,
case
when a.year = year(curdate() - interval 1 year) then a.goal else 0 end as real_goal_then,
case
when b.year = year(curdate()) then b.goal else 0 end as target_goal_now,
case
when b.year = year(curdate() - interval 1 year) then b.goal else 0 end as target_goal_then
from
(
select
id, name, year, sum(goal) goal
from
real
group by
id, year) a,
(
select
id, name, year, sum(goal) goal
from
target
group by
id, year) b
where
a.id = b.id
and a.year = b.year
group by
a.id, a.year) c
group by
id;

<?php
$sql = $pdo->prepare("SELECT *, 
SUM( case when YEAR(real.year) = YEAR(CURDATE())  then real.goal else 0 end) AS goal_now,
SUM( case when YEAR(real.year) = YEAR(CURDATE() - INTERVAL 1 YEAR)  then real.goal else 0 end) AS goal_then
FROM real
left join ( select *,
SUM( case when YEAR(target.year) = YEAR(CURDATE())  then target.goal else 0 end) AS goal_target,
SUM( case when YEAR(target.year) = YEAR(CURDATE() - INTERVAL 1 YEAR) then target.goal else 0 end) AS goal_target_then
from target
group by id_target ) as t
on real.id_real = t.id_target
group by real.id_real
having
real.id_real LIKE '1%'
");
$sql->execute(); 
while($data = $sql->fetch()){ 
?>