How to show total sum, running sum and quarterly sum from a table in oracle sql-CodePudding

Data:

NAME AGE  SAL  MONTH
A    21   100  JAN
B    22   150  FEB
C    21   200  SEP
D    22   100  OCT
EMP  21   150  DEC

Required Output::

1> Normal Sum

NAME AGE  SAL  SUM
A    21   100  700
B    22   150  700
C    21   200  700
D    22   100  700
EMP  21   150  700

2> Running Sum

NAME AGE  SAL  RUNNING_SUM
A    21   100  100
B    22   150  250
C    21   200  450
D    22   100  550
EMP  21   150  700

3> Quarterly Sum

NAME AGE  SAL  MONTH  SUM
A    21   100  JAN    250
B    22   150  FEB    250
C    21   200  SEP    200
D    22   100  OCT    250
EMP  21   150  DEC    250

Could someone please help me with the code to achieve the above results. I will be glad for the same.

CodePudding user response：

All of your results can be achieved by using the SUM aggregate function along with a window function.

--Normal Sum

SELECT s.*, SUM (sal) OVER () AS SUM
  FROM sum_data s;

--Running Sum

SELECT s.*, SUM (sal) OVER (ORDER BY name) AS SUM
  FROM sum_data s;

--Quarterly Sum

SELECT s.*,
       SUM (sal)
           OVER (
               PARTITION BY TO_CHAR (TO_DATE ('2022-' || s.month || '-1', 'YYYY-MON-DD'), 'Q'))    AS quarterly_SUM
  FROM sum_data s;