Aggregate the result in different years and subtract from 2 columns.Oracle-CodePudding

This is the query that where input- output = period
Is it possible to apply in this query to be input -output = result, so that I get the expected result.

SELECT date, period, SUM(period) OVER(ORDER BY date)
  FROM (SELECT EXTRACT(YEAR FROM date) date, SUM(period) period
          FROM YOUR_TABLE
         GROUP BY EXTRACT(YEAR FROM date));

Table

date,            input    output 

1-Aug-19,          2,      1    
1-Sep-19,          3,      1    
1-May-20,          3,     3   
1-Mar-20,          7,     2   
1-Apr-21,          3,     1   
1-Jul-21,          4,     2

expected result

period = input -output 
cumulative = period  period 


year,   period,     cumulative   

2019,       3,         3
2020,       5,         8
2021,       4,         12

CodePudding user response：

Is this maybe the answer that you are looking for?...

WITH
    tbl AS
        (
            Select To_Date('01-AUG-2019', 'dd-MON-yyyy') "DT", 2 "INP", 1 "OUTP" From DUAL UNION ALL
            Select To_Date('01-SEP-2019', 'dd-MON-yyyy') "DT", 3 "INP", 1 "OUTP" From DUAL UNION ALL
            Select To_Date('01-MAY-2020', 'dd-MON-yyyy') "DT", 3 "INP", 3 "OUTP" From DUAL UNION ALL
            Select To_Date('01-MAR-2020', 'dd-MON-yyyy') "DT", 7 "INP", 2 "OUTP" From DUAL UNION ALL
            Select To_Date('01-APR-2021', 'dd-MON-yyyy') "DT", 3 "INP", 1 "OUTP" From DUAL UNION ALL
            Select To_Date('01-JUL-2021', 'dd-MON-yyyy') "DT", 4 "INP", 2 "OUTP" From DUAL 
        )
SELECT
    YR,
    PERIOD,
    Sum(PERIOD) OVER(Order By YR ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) "CUMUL"
FROM
    (
        SELECT DISTINCT
          EXTRACT(YEAR FROM DT) "YR",
          Sum(INP- OUTP) OVER(Partition By EXTRACT(YEAR FROM DT) Order By EXTRACT(YEAR FROM DT)) "PERIOD"
        FROM
          tbl
        ORDER BY
          EXTRACT(YEAR FROM DT)
    )
--
--  R e s u l t
--  
--          YR     PERIOD      CUMUL
--  ---------- ---------- ----------
--        2019          3          3 
--        2020          5          8 
--        2021          4         12

CodePudding user response：

Solution for your problem:

SELECT
DISTINCT EXTRACT(YEAR FROM "date") as "yr",
SUM(input - output) OVER(PARTITION BY EXTRACT(YEAR FROM "date") ORDER BY EXTRACT(YEAR FROM "date")) as "period"
,SUM(input - output) OVER(ORDER BY EXTRACT(YEAR FROM "date")) as cumulative
FROM Table1;

DB Fiddle Link for working example