CODEWITHSUNDEEP
sqloracle-database
Omari Victor Omosa
Omari Victor Omosa

Reputation: 2879

Oracle sum group by column range with different starting points

I have below sample data. i want to find the total sum(amount) of each respective user_x but starting from sdate assigned to each user. the sdate in the respective users does not change

NDATE   |SDATE   |USER_X|AMOUNT|
--------|--------|------|------|
20210403|20210501|A     |     1|
20210402|20210501|A     |     2|
20210503|20210501|A     |     3|
20210506|20210501|A     |     4|
20210403|20210401|B     |     5|
20210503|20210401|B     |     2|
20210303|20210401|B     |     3|
20210403|20210511|C     |     3|
20210403|20210511|C     |     4|
20210403|20210511|C     |     5|

What i have tried below returns all the results, i just want one line result for each user_x

SELECT  
USER_x, 
sdate,
SUM(CASE WHEN  sdate < ndate THEN amount ELSE 0 END) OVER (PARTITION BY user_x ORDER BY ndate) sumation
FROM (   
SELECT  ndate, sdate, user_x, amount FROM ( 
SELECT  20210403 ndate, 20210501 sdate,  'A'   user_x ,  1 amount from dual UNION all
SELECT  20210402 ndate, 20210501 sdate,  'A'   user_x ,  2 amount from dual UNION ALL
SELECT  20210503 ndate, 20210501 sdate,  'A'   user_x ,  3 amount from dual UNION ALL
SELECT  20210506 ndate, 20210501 sdate,  'A'   user_x ,  4 amount from dual UNION ALL
SELECT  20210403 ndate, 20210401 sdate,  'B'   user_x ,  5 amount from dual UNION ALL
SELECT  20210503 ndate, 20210401 sdate,  'B'   user_x ,  2 amount from dual UNION ALL
SELECT  20210303 ndate, 20210401 sdate,  'B'   user_x ,  3 amount from dual UNION ALL
SELECT  20210403 ndate, 20210511 sdate,  'C'   user_x ,  3 amount from dual UNION ALL
SELECT  20210403 ndate, 20210511 sdate,  'C'   user_x ,  4 amount from dual UNION all
SELECT  20210403 ndate, 20210511 sdate,  'C'   user_x ,  5 amount from dual 
) )

This is what i want

USER_X|SDATE   |SUMATION|
------|--------|--------|
A     |20210501|       7| 
B     |20210401|       7|

How can i do this, also efficiently.

Upvotes: 1

Views: 39

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You seem to want:

select user_x, sdate, sum(amount)
from t
where ndate >= sdate
group by user_x, sdate;

Upvotes: 2

Related Questions