Sum of data at each row in different column in sql

Question

I need to create a different column of total sales which is the total of the sales units till that row. As our sales unit will increase, the total sales will increase row by row. I have attached the image to get the clear idea.

Screenshot

It looks like quite complicated solution. Can you please provide me any simple solution to this problem? Also, I can only use proc sql or SAS to find solution. Please help — 21Rahul, Apr 11 '18 at 04:06

Sandesh Gupta · Answer 1 · 2018-04-11T04:11:17.920

0

You can use the following query, which adds up all the sales upto that rowId and for the specific product.

select (select sum(ids)
         from TBL_NAME T1 
         where T1.rowid <= TBL_NAME.rowid  and PRODUCT=TBL_NAME.PRODUCT
       ) as TotalSales     
 from TBL_NAME;

Following answer is for Oracle DB, you can understand the query and easily convert it to the product you want.

edited Apr 11 '18 at 04:11

answered Apr 11 '18 at 04:04

Sandesh Gupta

1,175
2
11
19

score 0 · Accepted Answer · answered Apr 11 '18 at 04:14

One method is use to use of subquery with correlation approach

select *,  
         (select sum(sales) from table where product = t.product and ? <= t.?) TotalSales
from table t

However, you would required ? (i.e. rowid or id) column that could specify your column ordering.

Sum of data at each row in different column in sql

2 Answers2