Accumulative SUM Column

Question

Hi, I want to get a sum column with acumulative values from two Fields, for exmple if i select Fiscal_Year_BAL 2018 and Period_BAL 5 i want to get accumulative values from Fiscal_Year_BAL Some SELECT Clause or Sentenced to do it? Thanks..

Priyanka Jain · Answer

I have updated code as per you need to sum based from last row CREATE TABLE # temp ( fiscalYear INT , Period_BAL DECIMAL (8,2), Profit_Center DECIMAL (8,2) ) INSERT INTO # temp VALUES (2019,1,3.33300) INSERT INTO # temp VALUES (2018,4,6.33300) INSERT INTO # temp VALUES (2019,3,5.345500) INSERT INTO # temp VALUES (2018,2,4.33300) INSERT INTO # temp VALUES (2017,5,3.33300) INSERT INTO # temp VALUES (2016,4,7.33300) INSERT INTO # temp VALUES (2015,3,8.33300) INSERT INTO # temp VALUES (2018,2,9.33300) INSERT INTO # temp VALUES (2019,1,2.33300) INSERT INTO # temp VALUES (2017,1,1.33300) DECLARE @ year INT = 2019 DECLARE @pc INT = 6 SELECT fiscalYear, Period_BAL,RunningTotal FROM ( SELECT fiscalYear, Period_BAL, SUM (Profit_Center) OVER( ORDER BY fiscalYear DESC ,Period_BAL DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal FROM # temp WHERE fiscalYear year AND Period_BAL ) test ORDER BY fiscalYear ,Period_BAL The output will be shown as below,

Priyanka Jain · Answer

Updated Query with dynamic data CREATE TABLE # temp ( fiscalYear INT , Period_BAL DECIMAL (8,2), Profit_Center DECIMAL (8,2) ) INSERT INTO # temp VALUES (2019,1,3.33300) INSERT INTO # temp VALUES (2018,4,6.33300) INSERT INTO # temp VALUES (2019,3,5.345500) INSERT INTO # temp VALUES (2018,2,4.33300) INSERT INTO # temp VALUES (2017,5,3.33300) INSERT INTO # temp VALUES (2016,4,7.33300) INSERT INTO # temp VALUES (2015,3,8.33300) INSERT INTO # temp VALUES (2018,2,9.33300) INSERT INTO # temp VALUES (2019,1,2.33300) INSERT INTO # temp VALUES (2017,1,1.33300) DECLARE @ year INT = 2018 DECLARE @pc INT = 3 SELECT fiscalYear, Period_BAL, SUM (Profit_Center) OVER( ORDER BY fiscalYear,Period_Bal,Profit_Center ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal FROM # temp WHERE fiscalYear year AND Period_BAL ORDER BY fiscalYear, Period_BAL The output wil be displayed as below

Priyanka Jain · Answer

You can try query below to get your desired output, select t1.FisCal_Year, t1.ProfitCenter, SUM (t2.ProfitCenter) as sum from @t t1 inner join @t t2 on t1.FisCal_Year = t2.FisCal_Year AND t.ProfitCenter = t2.ProfitCenter Where t1.FiscalYear AND t1.ProfitCenter group by t1.FisCal_Year, t1.ProfitCenter order by t1.id

Ramon Quintana · Answer

Hi Priyanka, What i want is just create a accumulative´s values column with sum begins from last record. For example: I need another column with results from to sum 1963362209725.44 + 1565215511404.92 + etc Do i explain me?? Thanks again

Ramon Quintana · Answer

Hi Priyanka, First of all i really appreciate your time to response, well i tell that 2018 for Fiscal Year and 5 for Period are not a constant value, i mean if i select one year in his max value and select period in his max value i need to get the sum of all values minors of them, for example: if i select year 2019 and period 07, i need to all of values minors of these, i mean 2019 with his period Maybe i can add a new column with this calculated value in my main table witch has these two columns already.. Thank again

Accumulative SUM Column

Answers (5)