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"
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"