CODEWITHSUNDEEP
sqloracle-databasewindow-functions
user1945513
user1945513

Reputation: 41

Sum in Oracle window function

Can anyone tell me what is the significance of sum inside a sum (SUM(SUM(VAL))) in the oracle partition, in the following code snippet 

SELECT NMS,
       SUM(SUM(VAL)) OVER(PARTITION BY NMS),  ---- SIGNIFICANCE
       EMPNO,
       SUM(SUM(VAL)) OVER(PARTITION BY EMPNO)   ---- SIGNIFICANCE
  FROM PART_TEST_PART
 GROUP BY NMS, EMPNO;

Upvotes: 3

Views: 2318

Answers (1)

DazzaL
DazzaL

Reputation: 21993

its calculating the total SUM of VAL per NMS (for the first one) and EMPNO (for the second one).

the group by + SUM(VAL) is evaluated first, then the analytics compute on that result.

e.g. if you had the result after the group by of

EMPNO  NMS SUM(VAL)
1      a   15
1      b   24
2      a   50

you'd have SUM(SUM(VAL)) OVER(PARTITION BY NMS) as 

EMPNO  NMS SUM(VAL) SUM(SUM(VAL)) OVER(PARTITION BY NMS)
1      a   15       65  (15+50 for NMS = a)
1      b   24       24  (24 for NMS = b)
2      a   50       65  (15+50 for NMS = a)

you'd have SUM(SUM(VAL)) OVER(PARTITION BY EMPNO) as 

EMPNO  NMS SUM(VAL) SUM(SUM(VAL)) OVER(PARTITION BY EMPNO)
1      a   15       39  (15+24 for EMPNO = 1)
1      b   24       39  (15+24 for EMPNO = 1)
2      a   50       50  (50 for EMPNO = 2)

Upvotes: 6

Related Questions