Question 113628 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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

?>
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0 
    <?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()){ 
?>

  2. 0 
    
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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search