CODEWITHSUNDEEP
sqloracle-database
core_user
core_user

Reputation: 172

Oracle Sql cumulative sum by month and user

I have an table like below in oracle

USER YEAR MONTH POINT
1 2020 1 1
2 2020 1 1
3 2020 1 0
1 2020 2 1
2 2020 2 0
3 2020 2 1
1 2020 3 1
2 2020 3 0
3 2020 3 1

now I want to get table like below

USER YEAR MONTH POINT
1 2020 1 1
1 2020 2 1
1 2020 3 2
2 2020 1 1
2 2020 2 1
2 2020 3 1
3 2020 1 0
3 2020 2 1
3 2020 3 2

I tried below but not working what I expected

SELECT A_YEAR,A_MONTH,USER, SUM(POINTT) OVER (ORDER BY A_YEAR,A_MONTH,USER) CUM_POINT
FROM (
    SELECT WA.USER, WA.A_YEAR,WA.A_MONTH,1 AS POINTT FROM HRANALY.PUAN_ACTUAL PA
    INNER JOIN HRANALY.WAGE_ACTUAL WA ON WA.USER= PA.USER AND WA.A_YEAR = PA.A_YEAR AND WA.A_MONTH = PA.A_MONTH
    INNER JOIN HRANALY.PUAN_ACTUAL_DETAIL PAD ON PAD.REF_WAGE=PA.ID AND PAD.KALEM_KOD='GRUP_HEDEF' AND PAD.AMOUNT>0
    ORDER BY a_month
)
ORDER BY A_YEAR,A_MONTH,USER;

in this query CUM_POINT goes from 1 to n not working as user year month based How can I get second table with query.

Thanks in advance

Upvotes: 1

Views: 1362

Answers (2)

Popeye
Popeye

Reputation: 35920

You need analytical function as follows:

select user_id, year, month,
       sum(point) over (partition by user_id order by year, month) as points
  from t

In ytour query just add partition by clause as follows:

SUM(POINTT) OVER (PARTITION BY USER ORDER BY A_YEAR,A_MONTH,USER) CUM_POINT

Upvotes: 3

Oguen
Oguen

Reputation: 519

select x.*, 
       sum(point) over (partition by year, month, userid) sum_point
from foo x

enter image description here

Upvotes: 1

Related Questions