MariaDB: Running Total and Running Total by Group


DROP TEMPORARY TABLE IF EXISTS t1;
CREATE TEMPORARY TABLE t1 (id INT PRIMARY KEY, group_id INT, `value` INT);

INSERT t1 (id, group_id, `value`) 
SELECT				2, 1, 2 # Not in order
UNION ALL SELECT	1, 1, 1
UNION ALL SELECT	3, 1, 3
UNION ALL SELECT	4, 1, 4
UNION ALL SELECT	5, 2, 5
UNION ALL SELECT	7, 2, 7
UNION ALL SELECT	6, 2, 6;

-- Running Total
SET @`value` = 0;

SELECT
   A.*
   , (@`value` := @`value` + A.`value`) AS running_total
FROM  (SELECT * FROM  t1) AS A;

-- Running Total by Group
SET @`value` := 0;
SET @group_id = (SELECT group_id FROM t1 WHERE id = 1);

SELECT
   A.*
   , (@`value` := CASE WHEN group_id = @group_id THEN @`value` + `value` ELSE `value` END) AS running_total
   , @group_id := group_id AS group_rank
FROM (SELECT * FROM t1) AS A;

DROP TEMPORARY TABLE IF EXISTS t1;

Keep it simple :-)

Leave a comment

Your email address will not be published. Required fields are marked *