I have years and a percentage. The goal is to accumulate the current percentage in the accumulation column each year. The desired result I am looking for is in the accumulation column.

Тhis is a formula for converting percentages into numbers. PERCENTAGE/ 100 1

YEARS,          PERCENTAGE,      ACCUMULATIVE,
2010,           38.15%,           38.15%
2011,          -25.51%,           2,93%
2012,           -8.47%,           -5.80%,
2013,           18.51%,           11.64%
2014,            -2.07%,          9.32%
2015,            16.27%,          27.11%
2016,            108.94%,         165.60%
2017,            29.67%,          244.41%

I tried this but it didn't work

SELECT
     YEARS,
     PERCENTAGE,
    Sum(PERCENTAGE) OVER(Order By YR ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) "ACCUMULATIVE"
FROM
    (
        SELECT 
        YEAR  "YEAR",
         PERCENTAGE
           
          Sum(PERCENTAGE/100 1) OVER(Partition By (YEAR ) Order By (YEAR )) "PERCENTAGE"
        FROM
          tbl
        ORDER BY
       (YEAR )
    )

CodePudding user response：

You want the product (multiplication) of the rows, not the sum (addition).

There is not an analytic product function; but you can calculate it by using EXP(SUM(LN(value))):

SELECT YEARS,
       PERCENTAGE,
       ROUND(
         (EXP(Sum(LN(1   PERCENTAGE/100)) OVER(Order By Years)) - 1)*100,
         2
       ) AS ACCUMULATIVE
FROM   tbl

Which, for the sample data:

CREATE TABLE tbl (YEARS, PERCENTAGE) AS
SELECT 2010,  38.15 FROM DUAL UNION ALL
SELECT 2011, -25.51 FROM DUAL UNION ALL
SELECT 2012,  -8.47 FROM DUAL UNION ALL
SELECT 2013,  18.51 FROM DUAL UNION ALL
SELECT 2014,  -2.07 FROM DUAL UNION ALL
SELECT 2015,  16.27 FROM DUAL UNION ALL
SELECT 2016, 108.94 FROM DUAL UNION ALL
SELECT 2017,  29.67 FROM DUAL

Outputs:

YEARS	PERCENTAGE	ACCUMULATIVE
2010	38.15	38.15
2011	-25.51	2.91
2012	-8.47	-5.81
2013	18.51	11.63
2014	-2.07	9.32
2015	16.27	27.1
2016	108.94	165.57
2017	29.67	244.36

db<>fiddle here