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 :-)


About Peter Lalovsky

I am Microsoft SQL Server certified professional, creating with T-SQL, SSRS, SSIS, ASP.NET/C#, Azure, Python, PowerShell and more on a daily basis since year 2006. In 2016 i wrote a book for beginner and intermediate T-SQL programmers which you can download here. This blog is something like my personal programming documentation. When i am not in front of a computer, i am around my paper car – Trabant 601.

Leave a comment

Your email address will not be published.