Running Sums for Multiple Categories in MySQL

mysql sql cumulative-sum
Bogdan picture Bogdan · Feb 17, 2011 · Viewed 7.2k times · Source

I have a table of the form

 Category      Time      Qty  
     A           1        20 
     B           2         3
     A           3        43
     A           4        20
     B           5        25

I need a running total to be calculated by category in MySQL. The result would look something like this:

 Category      Time      Qty     Cat.Total  
     A           1        20         20
     B           2         3          3
     A           3        43         63
     A           4        20         83
     B           5        25         28

Any idea how I could do this efficiently in MySQL? I have searched far and wide, but all I can find is info on how to insert one single running total in MySQL. I wonder if there's any way to use GROUP BY or a similar construct to achieve this.

Answer

Andomar picture Andomar · Feb 17, 2011

You could calculate the sum in a subquery:

select  Category
,       Time
,       Qty
,       (
        select  sum(Qty) 
        from    YourTable t2 
        where   t1.Category = t2.Category 
                and t1.Time >= t2.Time
        ) as CatTotal
from    YourTable t1

Trading readability for speed, you can use a MySQL variable to hold the running sum:

select  Category
,       Time
,       Qty
,       @sum := if(@cat = Category,@sum,0) + Qty as CatTotal
,       @cat := Category
from    YourTable
cross join
        (select @cat := '', @sum := 0) as InitVarsAlias
order by
        Category
,       Time

The ordering is required for this construct to work; if you need a different order, wrap the query in a subquery:

select  Category
,       Time
,       Qty
,       CatTotal
from    (
        select  Category
        ,       Time
        ,       Qty
        ,       @sum := if(@cat = Category,@sum,0) + Qty as CatTotal
        ,       @cat := Category
        from    YourTable
        cross join
                (select @cat := '', @sum := 0) as InitVarsAlias
        order by
                Category
        ,       Time
        ) as SubQueryAlias
order by
        Time

Related questions

Create a Cumulative Sum Column in MySQL

I have a table that looks like this: id count 1 100 2 50 3 10 I want to add a new column called cumulative_sum, so the table would look like this: id count cumulative_sum 1 100 100 2 50 150 3 10 160 Is there a MySQL update statement that can do …

Read More
Cumulative sum over a set of rows in mysql

I have a complex query(containing multiple joins, unions) that returns a set of rows containing id, day, hr, amount. The output of the query looks like this: id day hr amount 1 1 1 10 1 1 2 25 1 1 3 30 1 2 1 10 1 2 2 40 1 2 2 30 2 1 1 10 2 1 2 15 2 1 3 30 2 2 1 10 2 2 2 20 2 2 2 30 I need to find cumulative total for each …

Read More
Calculate balance with mysql

I have a table which contains the following data: ID In Out 1 100.00 0.00 2 10.00 0.00 3 0.00 70.00 4 5.00 0.00 5 0.00 60.00 6 20.00 0.00 Now I need a query which gives me the following result: ID In Out Balance 1 100.00 0.00 100.00 2 10.00 0.00 110.00 3 0.00 70.00 40.00 4 5.00 0.00 45.00 5 0.00 60.00 -15.00 6 20.00 0.00 5.00 Is it possible to do this with one query, without using a …

Read More