cumulative calculation

Shambhu Rai 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

179777-image.png

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
0 comments No comments
{count} votes

4 answers

Sort by: Most helpful
  1. Erland Sommarskog 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. LiHong-MSFT 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


    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.


  3. Shambhu Rai 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

    180037-image.png

    0 comments No comments

  4. Shambhu Rai 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

    180153-image.png