cumulative values

Question

cumulative values

SSC-Addicted

Points: 497

March 3, 2022 at 8:11 pm

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


id1          id2   saledateid quantity consCalculation              Cimulative
10           10    2021040711     1(+0.5)(+0.33)(+0.25)(+0.2) 2.28
20102021040711     0.5(+0.33)(+0.25)(+0.2)  1.28
30102021040711     0.33(+0.25)(+0.2)          0.78
40102021040711     0.250.2                  0.45
50102021040711     0.2                   0.2



 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

This topic was modified 2 years, 9 months ago by Shree23.
This topic was modified 2 years, 9 months ago by Shree23.
This topic was modified 2 years, 9 months ago by Shree23.
This topic was modified 2 years, 9 months ago by Shree23.

Attachments:

You must be logged in to view attached files.

March 28, 2022 at 4:34 am

This was removed by the editor as SPAM

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

pietlinden SSC Guru Points: 63503 More actions · Answer 2

running total of 1 divided by ROW_NUMBER() over some partition? That's what the sequence {1, 0.5, 0.33, 0.25, 0.2} implies. Where's the problem? If you really had to, you could do a query on top of the running total. What's the point of this whole exercise?