mysqlmysql-variables

MySQL running total only working with hard coded integer


I have the following SQL which is not working how I thought it would.

SET @runtot:=0;
select 
q1.week,
(@runtot := @runtot + cast(q1.flying_hours as SIGNED)) as flying_hours,
q1.flying_hours as weekly_hours
from
(
select 
date_format(d.date,"%x%v") as week,
cast(ceil(sum(fh.engine_minutes)/60) as SIGNED) as flying_hours
from dates d 
left join flyinghours fh on date_format(fh.flight_start, "%x%v") = date_format(d.date,"%x%v")
where d.`date` >= "2019-01-01"
and d.date <= date_add(now(), interval 1 month)
group by date_format(d.date,"%x%v")
) q1
limit 20



The result is

"week"  "flying_hours"  "weekly_hours"
"201901"    \N  \N
"201902"    \N  \N
"201903"    \N  \N
"201904"    \N  \N
"201905"    \N  "20"
"201906"    \N  "9"
"201907"    \N  \N
"201908"    \N  \N
"201909"    \N  \N
"201910"    \N  \N
"201911"    \N  \N
"201912"    \N  \N
"201913"    \N  \N
"201914"    \N  \N
"201915"    \N  \N
"201916"    \N  \N
"201917"    \N  "7"
"201918"    \N  "39"
"201919"    \N  "93"
"201920"    \N  "9"

However is I replace the flying_hours as a hard coded interger in the sub query it works.

SET @runtot:=0;
select 
q1.week,
(@runtot := @runtot + cast(q1.flying_hours as SIGNED)) as flying_hours,
q1.flying_hours as weekly_hours
from
(
select 
date_format(d.date,"%x%v") as week,
5 as flying_hours
from dates d 
left join flyinghours fh on date_format(fh.flight_start, "%x%v") = date_format(d.date,"%x%v")
where d.`date` >= "2019-01-01"
and d.date <= date_add(now(), interval 1 month)
group by date_format(d.date,"%x%v")
) q1
limit 20

"week"  "flying_hours"  "weekly_hours"
"201901"    "5" "5"
"201902"    "10"    "5"
"201903"    "15"    "5"
"201904"    "20"    "5"
"201905"    "25"    "5"
"201906"    "30"    "5"
"201907"    "35"    "5"
"201908"    "40"    "5"
"201909"    "45"    "5"
"201910"    "50"    "5"
"201911"    "55"    "5"
"201912"    "60"    "5"
"201913"    "65"    "5"
"201914"    "70"    "5"
"201915"    "75"    "5"
"201916"    "80"    "5"
"201917"    "85"    "5"
"201918"    "90"    "5"
"201919"    "95"    "5"
"201920"    "100"   "5"


Solution

  • The problem is because some of the values were null. When I use ifnull() to replace with a zero it all works.

    SET @runtot:=0;
    select 
    q1.week,
    @runtot := @runtot + ifnull(q1.flying_hours,0) as flying_hours,
    q1.flying_hours as weekly_hours
    from
    (
    select 
    date_format(d.date,"%x%v") as week,
    cast(ceil(sum(fh.engine_minutes)/60) as SIGNED) as flying_hours
    from dates d 
    left join flyinghours fh on date_format(fh.flight_start, "%x%v") = date_format(d.date,"%x%v")
    where d.`date` >= "2019-01-01"
    and d.date <= date_add(now(), interval 1 month)
    group by date_format(d.date,"%x%v")
    ) q1
    limit 20
    
    "week"  "flying_hours"  "weekly_hours"
    "201901"    "0" \N
    "201902"    "0" \N
    "201903"    "0" \N
    "201904"    "0" \N
    "201905"    "20"    "20"
    "201906"    "29"    "9"
    "201907"    "29"    \N
    "201908"    "29"    \N
    "201909"    "29"    \N
    "201910"    "29"    \N
    "201911"    "29"    \N
    "201912"    "29"    \N
    "201913"    "29"    \N
    "201914"    "29"    \N
    "201915"    "29"    \N
    "201916"    "29"    \N
    "201917"    "36"    "7"
    "201918"    "75"    "39"
    "201919"    "168"   "93"
    "201920"    "177"   "9"