Leaderboard

"absents" is a column alias. You can't reference an alias inside the SELECT or WHERE parts of the same query. If you want the cumulative then you'll need an extra subquery SELECT recno , timeoflogout , absents , @tot := @tot + IFNULL(absents,0) as total FROM ( SELECT recno , DATEDIFF(@prevlog, timeoflogout) - 1 as absents , @prevlog := timeoflogout as timeoflogout FROM ( SELECT recno , timeoflogout FROM ajoo ORDER BY timeoflogout DESC ) as sorted JOIN (SELECT @prevlog := NULL, @tot := 0) as initialise ) recs; +-------+---------------------+---------+-------+ | recno | timeoflogout | absents | total | +-------+---------------------+---------+-------+ | 30 | 2019-10-24 17:37:35 | NULL | 0 | | 29 | 2019-10-21 15:23:54 | 2 | 2 | | 28 | 2019-10-19 12:31:23 | 1 | 3 | | 27 | 2019-10-18 16:37:43 | 0 | 3 | | 26 | 2019-10-15 16:56:55 | 2 | 5 | | 25 | 2019-10-05 11:18:18 | 9 | 14 | | 24 | 2019-10-04 21:41:17 | 0 | 14 | | 23 | 2019-10-03 13:28:17 | 0 | 14 | | 21 | 2019-10-02 07:47:35 | 0 | 14 | | 4 | 2019-09-30 13:13:15 | 1 | 15 | +-------+---------------------+---------+-------+ "absents" is now a column in the subquery (a dynamic temporary table)

This will calculate the "absents" value between consecutive logouts SELECT recno , DATEDIFF(@prevlog, timeoflogout) - 1 as absents , @prevlog := timeoflogout as timeoflogout FROM ( SELECT recno , timeoflogout FROM ajoo ORDER BY timeoflogout DESC ) as sorted JOIN (SELECT @prevlog:=NULL) as initialise +-------+---------+---------------------+ | recno | absents | timeoflogout | +-------+---------+---------------------+ | 30 | | 2019-10-24 17:37:35 | | 29 | 2 | 2019-10-21 15:23:54 | | 28 | 1 | 2019-10-19 12:31:23 | | 27 | 0 | 2019-10-18 16:37:43 | | 26 | 2 | 2019-10-15 16:56:55 | | 25 | 9 | 2019-10-05 11:18:18 | | 24 | 0 | 2019-10-04 21:41:17 | | 23 | 0 | 2019-10-03 13:28:17 | | 21 | 0 | 2019-10-02 07:47:35 | | 4 | 1 | 2019-09-30 13:13:15 | +-------+---------+---------------------+ You can accumulate the total of "15" as you process the query results.

A few notes about text bounding boxes which, I hope, will help in precise placement of your text. Suppose I have the text string "The lazy fox" which I want to display using 150pt Vivaldi . My image is 4896 x 3672 and I want the text placed at the bottom right but 250 pixels from the edges of the image. $box = imagettfbbox(150,0,'c:/windows/fonts/vivaldii.ttf','The lazy fox'); gives this array of coordinates of the four corners $box = Array ( [0] => 23 [1] => 55 [2] => 871 [3] => 55 [4] => 871 [5] => -140 [6] => 23 [7] => -140 ) You may wonder why it can't just give a rectangle from (0,0) to (width, height) to make sizing simple, but there is extra information to be extracted from the array Text width = (871 - 23) = 848 Text height = 55 - (-140) = 195 The baseline will be 140px from the top The text is offset 23 px to the right. My text, therefore, will be in a rectangle 848 x 195 positioned 250 px from right and bottom edges. The top left x coord of the rectangle will be (4896 - 250 - 848) = 3798 and top left y coord will be (3672 - 250 - 195) = 3227. However, to land the text precisely into this area we position it on the baseline and at the required x offset, ie (3798 - 23 , 3227 + 140) = (3775, 3367). I use a simple custom function to assist with this process function metrics($font, $fsize, $str) { $box = imagettfbbox($fsize, 0, $font, $str); $ht = abs($box[5] - $box[1]); $wd = abs($box[4] - $box[0]); $base = -$box[5]; $tx = -$box[0]; return [ 'width' => $wd, 'height' => $ht, 'ascent' => $base, 'offsetx' => $tx ]; } $box = metrics ('c:/windows/fonts/vivaldii.ttf', 150, 'The lazy fox'); $box = Array ( [width] => 848 [height] => 195 [ascent] => 140 [offsetx] => -23 )

Alternative model which allows multiple siblings jdev_nroll; jdev_sibling; +----+--------+---------+-------+-----------+------------+ +------------+----------+ | id | sname | ctclass | shift | ctstudent | dob | | sibling_id | elder_id | +----+--------+---------+-------+-----------+------------+ +------------+----------+ | 1 | Curly | 1 | 0 | N | 2007-01-20 | | 2 | 1 | | 2 | Larry | 1 | 0 | Y | 2010-12-21 | | 3 | 1 | | 3 | Mo | 1 | 0 | Y | 2011-02-22 | | 3 | 2 | | 4 | Peter | 1 | 0 | N | 2009-01-03 | | 4 | 5 | | 5 | Paul | 1 | 0 | N | 2006-12-21 | | 9 | 8 | | 6 | Mary | 1 | 0 | Y | 2010-09-20 | | 9 | 10 | | 7 | Jane | 1 | 0 | N | 2008-03-08 | | 10 | 8 | | 8 | John | 1 | 0 | N | 2006-10-04 | +------------+----------+ | 9 | George | 1 | 0 | Y | 2010-10-26 | | 10 | Ringo | 1 | 0 | Y | 2009-11-15 | +----+--------+---------+-------+-----------+------------+ SELECT a.id as sibling_id , a.sname as sibling_name , TIMESTAMPDIFF(YEAR,a.dob,curdate()) as sibling_age , a.ctclass as class , b.id as elder_id , b.sname as elder_name , TIMESTAMPDIFF(YEAR,b.dob,curdate()) as elder_age , b.ctstudent as elder_ctstudent FROM jdev_nroll a JOIN jdev_sibling s ON a.id = s.sibling_id JOIN jdev_nroll b ON s.elder_id = b.id WHERE a.ctstudent = 'Y' ORDER BY a.id +------------+--------------+-------------+-------+----------+------------+-----------+-----------------+ | sibling_id | sibling_name | sibling_age | class | elder_id | elder_name | elder_age | elder_ctstudent | +------------+--------------+-------------+-------+----------+------------+-----------+-----------------+ | 2 | Larry | 9 | 1 | 1 | Curly | 13 | N | | 3 | Mo | 8 | 1 | 1 | Curly | 13 | N | | 3 | Mo | 8 | 1 | 2 | Larry | 9 | Y | | 9 | George | 9 | 1 | 8 | John | 13 | N | | 9 | George | 9 | 1 | 10 | Ringo | 10 | Y | | 10 | Ringo | 10 | 1 | 8 | John | 13 | N | +------------+--------------+-------------+-------+----------+------------+-----------+-----------------+