Running Sums for Multiple Categories in MySQL

Question

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.

Does it have to be a running total or is it ok to calculate the final total in one go? — fredley
– fredley, Commented Feb 17, 2011 at 17:35
i need running total for this @fredley Do u have any idea how to this? let's say I have the same tables as he has — AyukNayr
– AyukNayr, Commented Feb 20, 2018 at 5:09

Andomar · Accepted Answer · 2011-02-17 17:46:37Z

14

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

edited Feb 17, 2011 at 17:46

answered Feb 17, 2011 at 17:35

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ryan Over a year ago

How well does this perform

smorhaim Over a year ago

This solution does not consider the Time column. It does work in part... but the running total with time does not work, only with category alone. -- I am still working on a solution.

Collectives™ on Stack Overflow

Running Sums for Multiple Categories in MySQL

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related