Accumulative SUM Column

Ramon Quintana

7y1.1k

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<=2018 and Period_BAL<=5

Some SELECT Clause or Sentenced to do it?

Thanks..

5 Replies

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

Priyanka Jain7y ago
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 <= @pc
) test
ORDER BY fiscalYear ,Period_BAL
The output will be shown as below,
Ramon Quintana7y ago
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
Priyanka Jain7y ago
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 <= @pc
ORDER BY fiscalYear, Period_BAL
The output wil be displayed as below
Ramon Quintana7y ago
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 <=7 + 2018 + all his periods + 2017 + all his periods, etc until find minor year´s value.

Maybe i can add a new column with this calculated value in my main table witch has these two columns already..

Thank again
Priyanka Jain7y ago
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 <= 2018 AND t1.ProfitCenter <= 5
group by t1.FisCal_Year, t1.ProfitCenter
order by t1.id