CODEWITHSUNDEEP
sqloracle-database
KarmaOfJesus
KarmaOfJesus

Reputation: 101

Oracle sql: sum rows in following ranges

Assume following table named t1:

create table t1(
    dates date,
    prod number
);

insert into t1 values('30.09.2013', 1);
insert into t1 values('30.09.2013', 2);
insert into t1 values('22.06.2017', 1);
insert into t1 values('22.06.2017', 1);
insert into t1 values('22.06.2017', 1);
insert into t1 values('19.03.2019', 2);
insert into t1 values('18.07.2019', 1);
insert into t1 values('18.07.2019', 1);

DATE        PROD 
30.09.2013  1    
30.09.2013  2    
22.06.2017  1    
22.06.2017  1    
22.06.2017  1    
19.03.2019  2    
18.07.2019  1    
18.07.2019  1

What I'm struggling to do is to add new column to this table which would sum values in PROD column but inside range of 2 dates which greater then date in current row.

Result should be as following:

DATE        PROD CNT
30.09.2013  1    5
30.09.2013  2    5
22.06.2017  1    4
22.06.2017  1    4
22.06.2017  1    4
19.03.2019  2    2
18.07.2019  1    NULL
18.07.2019  1    NULL

I'm trying this code:

select t1.*, 
sum(prod) over(order by date range between 1 following and 2 following)
from t1 t1;

And i get unexpected result:

DATE        PROD CNT
30.09.2013  1    NULL
30.09.2013  2    NULL
22.06.2017  1    NULL
22.06.2017  1    NULL
22.06.2017  1    NULL
19.03.2019  2    NULL
18.07.2019  1    NULL
18.07.2019  1    NULL

It is obvious that I either treat FOLLOWING operators incorrectly or problem hides in date type but anyway i can't find the solution. So my question is where I made a mistake?

Appreciate your help

Upvotes: 0

Views: 722

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271013

Your dates are not consecutive, so you need to convert them to a number:

select t1.*, 
    sum(prod) over (order by date_num range between 1 following and 2 following)
from (select t1.*,
             dense_rank() over (order by dates) as date_num
      from t1
     ) t1;

Here is a SQL Fiddle.

Upvotes: 1

Related Questions