It's dificil ton explain, i want to Sum the 3 last row for every row. An exemple will be better :
ID VALUE1 RESULT I WANT
===================
1 10 10+5+20 = 35
2 5 5+20+4 = 29
3 20 20+4+50 = 74
4 4 4+50+300 = 354
5 50 50+300+10 = 360
6 300 300+10+15 = 325
7 10 NULL
8 15 NULL
I use Oracle SQL. Thanks
with dummy as
(
select 10 as value from dual union all
select 5 from dual union all
select 20 from dual union all
select 4 from dual union all
select 50 from dual union all
select 300 from dual union all
select 10 from dual union ALL
select 15 from dual
)
SELECT value, SUM(value) FROM dummy GROUP BY value
i dont have idea to how to process
To get result just for rows having 3 values summed - use conditional Count() Over() analytic function:
WITH -- S a m p l e D a t a :
dummy as
( Select 1 "ID", 10 "VALUE" From Dual Union All
Select 2, 5 From Dual Union All
Select 3, 20 From Dual Union All
Select 4, 4 From Dual Union All
Select 5, 50 From Dual Union All
Select 6, 300 From Dual Union All
Select 7, 10 From Dual Union All
Select 8, 15 From Dual
)
-- S Q L :
SELECT ID, VALUE,
Case When Count(ID) OVER (ORDER BY ID ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) = 3
Then Sum(VALUE) OVER (ORDER BY ID ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING)
End "TOTAL_3_ROWS"
FROM dummy;
/* R e s u l t :
ID VALUE TOTAL_3_ROWS
-- ----- -------------
1 10 35
2 5 29
3 20 74
4 4 354
5 50 360
6 300 325
7 10 null
8 15 null */