CODEWITHSUNDEEP
oracle-databaseoracle11g
DRTauli
DRTauli

Reputation: 771

Oracle sum group by date range

I have the following table

+-----------+-------+-------+
| Date      | Type  | Value |
+-----------+-------+-------+
| 1/1/2013  | A     | 1     |
| 1/2/2013  | A     | 3     |
| 1/3/2013  | A     | 5     |
| 1/4/2013  | A     | 6     |
| 1/6/2013  | A     | 8     |
| 1/7/2013  | A     | 1     |
| 1/8/2013  | A     | 2     |
+-----------+-------+-------+

I want to sum the value for the previous 3 dates for a certain day so i used this query. ie: sel_date = 1/3/2013.

select type, sum(value)
from table_name
where date <= seldate
and date > seldate - 3
group by type

Now the problem is, I want to output a table with a given date range computing for the previous 3 days for each date.

ie: sel_date range 1/3/2013 - 1/8/2013

+-----------+-------+------------+
| Date      | Type  | Sum(Value) |
+-----------+-------+------------+
| 1/3/2013  | A     | 9          | // 5 + 3 + 1
| 1/4/2013  | A     | 14         | // 6 + 5 + 3
| 1/5/2013  | A     | 11         | // 0 + 6 + 5
| 1/6/2013  | A     | 14         | // 8 + 0 + 6
| 1/7/2013  | A     | 9          | // 1 + 8 + 0
| 1/8/2013  | A     | 11         | // 2 + 1 + 8
+-----------+-------+------------+

Is there a way to do this in a single query. I tried reading on partitioning but it is leading me no where.

Upvotes: 2

Views: 10857

Answers (4)

B. Khan
B. Khan

Reputation: 105

select t.date, sum(t.value) OVER(ORDER BY t.date ROWS BETWEEN 2 PRECEDING AND 0 FOLLOWING) as Pre_3row_sum
from table_name t

Upvotes: 0

Aleksej
Aleksej

Reputation: 22949

You can try with something like this:

with test(Date_, Type, Value ) as
(
  select to_date('01/01/2013', 'mm/dd/yyyy'), 'A', 1 from dual union all
  select to_date('01/02/2013', 'mm/dd/yyyy'), 'A', 3 from dual union all
  select to_date('01/03/2013', 'mm/dd/yyyy'), 'A', 5 from dual union all
  select to_date('01/04/2013', 'mm/dd/yyyy'), 'A', 6 from dual union all
  select to_date('01/05/2013', 'mm/dd/yyyy'), 'A', 8 from dual union all
  select to_date('01/06/2013', 'mm/dd/yyyy'), 'A', 1 from dual union all
  select to_date('01/07/2013', 'mm/dd/yyyy'), 'A', 2 from dual
)
select *
from (
        select date_, type,
               value + nvl(lag(value, 1) over (partition by type order by date_), 0)
                     + nvl(lag(value, 2) over (partition by type order by date_), 0) as value
        from test
)        
where date_ between to_date('01/03/2013', 'mm/dd/yyyy') and to_date('01/07/2013', 'mm/dd/yyyy')

This sums, for each row, the values of the two preceding ones, based on date; the external query is simply used to apply the filter, given that applying it in the internal query would lead to a wrong sum.

The LAG is used to read values from the rows that precede the current row by 1 or 2 positions.

Upvotes: 2

Pham X. Bach
Pham X. Bach

Reputation: 5432

You can use this:

select date1 ,type,
     (select sum(t1.value) sumvalue from table_name t1 where t1.date1 between (t2.date1 - 2) and t2.date1 )
from table_name t2
where date1 between startDate and endDate

Upvotes: 1

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

Use range between in windowing clause:

select dt, type, value, 
       sum(value) over (order by dt range between 2 preceding and current row) as sv
  from t

Test data and output:

create table t (dt date, type varchar2(1), value number(5));
insert into t values (date '2013-01-01', 'A', 1);
insert into t values (date '2013-01-02', 'A', 3);
insert into t values (date '2013-01-03', 'A', 5);
insert into t values (date '2013-01-04', 'A', 6);
insert into t values (date '2013-01-05', 'A', 8);
insert into t values (date '2013-01-06', 'A', 1);
insert into t values (date '2013-01-07', 'A', 2);
insert into t values (date '2013-01-12', 'A', 2);

DT          TYPE  VALUE         SV
----------- ---- ------ ----------
2013-01-01  A         1          1
2013-01-02  A         3          4
2013-01-03  A         5          9
2013-01-04  A         6         14
2013-01-05  A         8         19
2013-01-06  A         1         15
2013-01-07  A         2         11
2013-01-12  A         2          2

Upvotes: 5

Related Questions