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

Question

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

?>

Please publish sample data and expected output as text in the question. (we can't use images and links, questions should be self contained where possible) — P.Salmon, Sep 19 '22 at 06:21
It looks like the duplicates are in the real table but I could be wrong - where do you think they are? — P.Salmon, Sep 19 '22 at 06:27
[Two SQL LEFT JOINS produce incorrect result](https://stackoverflow.com/q/12464037/3404097) — philipxy, Sep 19 '22 at 07:47
Debug questions require a [mre]--cut & paste & runnable code including initialization; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For SQL include DDL & tabular initialization code. For debug that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. [ask] [Help] When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. (Debugging fundamental.) — philipxy, Sep 19 '22 at 11:21
Please ask 1 specific researched non-duplicate question. Please either ask about 1 bad query/function with the obligatory [mre] & why you think it should return something else at the 1st subexpression that it doesn't give what you expect, justified by reference to authoritative documentation, or ask about your overall goal giving working parts you can do & ideally a [mre]. But please ask about the former 1st because misconceptions in the former will get in the way of understanding the latter. And bad code doesn't tell us what you wish it would do. [ask] [Help] — philipxy, Sep 19 '22 at 11:34

score 0 · Answer 1 · edited Sep 19 '22 at 13:08


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;

https://meta.stackoverflow.com/questions/251361/how-do-i-format-my-code-blocks — P.Salmon, Sep 19 '22 at 09:39
[answer] [Help] Code-only answers are poor. Explain why this answers. — philipxy, Sep 19 '22 at 11:19
Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). — Community, Sep 23 '22 at 05:15

rumah dijual bandung · Answer 2 · 2022-09-19T17:06:30.003

-1

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

edited Sep 19 '22 at 17:06

answered Sep 19 '22 at 08:25

rumah dijual bandung

1
1

1

The shown SQL code is not valid. (`....left join ( SUM( case when .....`) – Luuk Sep 19 '22 at 09:19
[answer] [Help] Code-only answers are poor. Explain why this answers. Please avoid greetings, thanks, etc. – philipxy Sep 19 '22 at 13:14
As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Sep 23 '22 at 05:37

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

2 Answers2