cumulative calculation

Question

Hi Expert,

i wanted to get cumulative values as a new column in below table,

--create table main2( oid int, OID2 int,SaleDateId date, quantity int,cons int)

insert main1
values

(1,10,'20240307',1,1),
(2,10,'20240307',1,1),
(3,10,'20240307',1,1),
(4,10,'20240307',1,1),
(5,10,'20240307',1,1),

calculation:

Cons / sum of total quantity order by id and equally distributed in rows
first row: 1/1 =1
2nd row : 1/2
3 rd row: 1/3
4 th row: 1/4
5 th row: 1/5
All output values should equally divided into previous rows like below

expected output..attached in image unable to paste the table here

i tried

select oid,oid2, sum (quantity) as quantity, sum(cons) as cons, SUM(FLOOR(cons/quantity*10)/10)
OVER(ORDER BY oid DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as cumulative --select *
from main2 group by oid,oid2,quantity,cons
but unable to get right output

Answer 1

You first need to number the rows in ascending order to get a row number and then divide quantity/cons by this number, and then compute a running sum in reverse order from this.

Here is a query:

WITH numbering AS (
   SELECT oid, OID2, SaleDateId, quantity, cons,
          row_number() OVER (ORDER BY oid) as rowno
   FROM   main2
)
SELECT oid, OID2, SaleDateId, 
       cast (SUM(1E0*(cons/quantity) / rowno) OVER (ORDER BY rowno DESC
                                              ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
             AS decimal(10,2)) AS Cumulative
FROM   numbering
ORDER  BY oid

Answer 2

Hi @Shambhu Rai
Check this：

;WITH CTE AS  
(SELECT *,SUM(quantity)OVER(PARTITION BY OID2 ORDER BY oid ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS SUM_quantity,  
          CAST((cons*1.0 / SUM(quantity)OVER(PARTITION BY OID2 ORDER BY oid ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))AS decimal(10,2)) AS DEV_quantity  
 FROM main2  
)  
SELECT oid, OID2,SaleDateId, quantity,cons,   
       SUM(DEV_quantity)OVER(PARTITION BY OID2 ORDER BY oid DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Cimulative_SUM  
FROM CTE  
ORDER BY oid   

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Currently this value is coming as per the calculation. I am expecting values in order below

Expected output

Answer 4

Hi,
the logic is missing the expected on is below

truncate table main1

insert main1
values
(5,1,20210407',1,100),
(6,1,20210412',1,10),
(7,2,20210412',1,10)

output is expcted