All geek questions in one place

Running sums for multiple categories in MySQL

I have a view table

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

I need the total amount to be calculated by categories in MySQL. The result will 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 searched around the world, but all I can find is information on how to insert one single total in MySQL. I wonder if there is a way to use GROUP BY or a similar construct for this.

+4
source share
1 answer

You can calculate the amount 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

For readability, for speed, you can use the MySQL variable to store the current amount:

 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

This design requires ordering; if you need a different order, wrap the request 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
+10
source

Source: https://habr.com/ru/post/1340205/

More articles:


All Articles