How to sum values for to get a accumulative total value

Ramon Quintana

7y1.2k

Hi,

Just i want to create a new column from sum accumulative values begins from last record:

for example in this image it is used a OVER (PARTITION BY clause in the field CATEGORY to calculates retencion_value_3_week , but i want to keep the values from the first record to get a TOTAL accumulative value at the end or records:

I mean not to get "6" in the first record of category B in retencion_value_3_week, but "26" and after sum "15" to get not "21" but "41"

Thanks...

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Kumar7y ago
Hi, Go through the below link sure it will help you.same thing is given https://www.dotnettechpoint.com/2018/03/calculate-running-total-in-sql-serverr.html?m=1
Ramon Quintana7y ago
Hi NAGARAJU,

A question, if i don´t know exactly the total´s rows number?

You write "ORDER BY YEAR ROWS 10 PRECEDING" for this example in particular because we know how many records there are, but i run a query without know how many rows i will get??

I mean, i would like to know if i can make the sum from last record to the first??

"SELECT CATEGORY,YEAR,WEEK,VALUE, SUM(VALUE) OVER
(ORDER BY YEAR ROWS 10 PRECEDING) AS RETENTION_VALUE FROM ##t
ORDER BY CATEGORY,YEAR,WEEK"

Thank u...

Hi AMI,

i will try to do this one too..

Thanks a lot to both of you mate!!
nagaraju yenumula7y ago
SELECT CATEGORY,YEAR,WEEK,VALUE, SUM(VALUE) OVER
(ORDER BY YEAR ROWS 10 PRECEDING) AS RETENTION_VALUE FROM ##t
ORDER BY CATEGORY,YEAR,WEEK
Amit Mohanty7y ago
Hey try this.
DECLARE @COUNT INT = (SELECT COUNT(*)-1 FROM TBL1)
DECLARE @SQLQRY NVARCHAR(MAX) = '
SELECT CATEGORY,YEAR,WEEK,VALUE, SUM(VALUE) OVER
(ORDER BY YEAR ROWS '+CAST(@COUNT AS VARCHAR)+' PRECEDING) AS RETENTION_VALUE FROM TBL1
ORDER BY CATEGORY,YEAR,WEEK'
PRINT @SQLQRY
EXEC SP_EXECUTESQL @SQLQRY;