How to sum values for to get a accumulative total value

Question

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

Amit Mohanty · Answer

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;

nagaraju yenumula · Answer

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 Kumar · Answer

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 Quintana · Answer

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

How to sum values for to get a accumulative total value

Answers (4)