# cumulative calculation

1,406 Reputation points
2022-03-03T20:31:43.527+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,850 questions
SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,818 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,559 questions

1. 102.2K Reputation points
2022-03-03T22:29:56.737+00:00

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
``````

2. 10,046 Reputation points
2022-03-04T01:29:15.223+00:00

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

3. 1,406 Reputation points
2022-03-04T08:16:24.927+00:00

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

Expected output

4. 1,406 Reputation points
2022-03-04T14:46:29.947+00:00

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