CODEWITHSUNDEEP
mysqlrowaggregate
Karunakar
Karunakar

Reputation: 2349

aggregate values of a column upto that row for each row mysql

I have a table with the following fields: 

season, collection, product_key, units_sold

I want to add extra 

cumulative_sold column(aggreate of previous rows values)

which should satisfy order by season, collection,units_sold

sample input
---------- 
ss,f1,1,3
ss,f1,2,4
ss,f1,3,4
ss,f2,1,1
ss,f2,5,1

expected output 
--------------
ss,f1,1,3,3 
ss,f1,2,4,7(3+4)
ss,f1,3,4,11(3+4+4)
ss,f2,1,1,1
ss,f2,5,1,2(1+1)

Upvotes: 4

Views: 467

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

You can do a cumulative sum using a correlated subquery:

select season, collection, product_key, units_sold,
       (select sum(units_sold)
        from t t2
        where t2.season < t.season or
              t2.season = t.season and t2.collection < t.collection or
              t2.season = t.season and t2.collection = t.collection and t2.units_sold <= t.units_sold
       ) as cumsum            
from t;

This is standard SQL. If you have a significant amount of data, you will want an index on t(season, collection, units_sold).

EDIT:

If you want this only for a particular collection in a particular season, rather than an overall cumulative sum:

select season, collection, product_key, units_sold,
       (select sum(units_sold)
        from t t2
        where t2.season = t.season and t2.collection = t.collection and
              t2.units_sold <= t.units_sold
       ) as cumsum            
from t;

EDIT II:

This is a pretty standard type of SQL. It would help if you would get the question correct. To handle duplicates on units_sold:

select season, collection, product_key, units_sold,
       (select sum(units_sold)
        from t t2
        where t2.season = t.season and t2.collection = t.collection and
              (t2.units_sold < t.units_sold or
               t2.units_sold = t.units_sold and t2.product_key <= t.product_key
              )
       ) as cumsum            
from t;

Upvotes: 1

Related Questions