How to Sum "case when" clause after left join without duplication-CodePudding

How do I get the SUM of an amount in a CASE WHEN clause?

Table real:

id	name	goal	year
10	ronaldo	5	2022
10	ronaldo	5	2022
11	messi	5	2022
11	messi	5	2022
10	ronaldo	10	2021
11	messi	10	2021

Table target:

id	name	goal	year
10	ronaldo	10	2022
11	messi	10	2022
10	ronaldo	10	2021
11	messi	10	2021

I tried inner join, but the result was wrong:

id	name	real 2022	target 2022	real 2021	target 2021
10	ronaldo	20	30	20	30
11	messi	20	30	20	30

Desired result:

id	name	real 2022	target 2022	real 2021	target 2021
10	ronaldo	10	10	10	10
11	messi	10	10	10	10

<?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,
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 real
left join target
on id_real = id_target
group by real.id_real
having
real.id_real LIKE '1%'
");
$sql->execute(); 

while($data = $sql->fetch()){ 

?>

CodePudding user response：


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;

CodePudding user response：

<?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 (
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()){ 
?>