Query Help

[DM]

I am trying to create a query to get the results below. What I want
is a query that selects for a given set of years and then totals the
amounts per year as a rolling total. I can do this by using pl/sql
using UNION for each given year. If possible would like to do this in
a sql query. If possible I am assuming I will need to use some use of
CUBE or/and ROLLUP. Any ideas???

table1

year col1
2003 10
2004 10
2005 10
2006 10
2007 10
2008 10

results:

year resultcol1
2003 10
2004 20
2005 30
2006 40
2007 50
2008 60

[Romeo Olympia]

This is a classic case of cumulative aggregates using oracle analytics
(since 8i). You might wanna read up on that.

For your query on hand:

select year,
sum(col1) over (order by year rows unbounded preceding) resultcol1
from table1
order by year

YEAR RESULTCOL1
---------- ----------
2003 10
2004 20
2005 30
2006 40
2007 50
2008 60

Hope that helps.

Cheers,

Romeo

ma**********@hotmail.com (DM) wrote in message news:<bf************************@posting.google.co m>...

I am trying to create a query to get the results below. What I want
is a query that selects for a given set of years and then totals the
amounts per year as a rolling total. I can do this by using pl/sql
using UNION for each given year. If possible would like to do this in
a sql query. If possible I am assuming I will need to use some use of
CUBE or/and ROLLUP. Any ideas???

table1

year col1
2003 10
2004 10
2005 10
2006 10
2007 10
2008 10

results:

year resultcol1
2003 10
2004 20
2005 30
2006 40
2007 50
2008 60

[DM]

> ma**********@hotmail.com (DM) wrote in message news:<bf************************@posting.google.co m>...

I am trying to create a query to get the results below. What I want
is a query that selects for a given set of years and then totals the
amounts per year as a rolling total. I can do this by using pl/sql
using UNION for each given year. If possible would like to do this in
a sql query. If possible I am assuming I will need to use some use of
CUBE or/and ROLLUP. Any ideas???

table1

year col1
2003 10
2004 10
2005 10
2006 10
2007 10
2008 10

results:

year resultcol1
2003 10
2004 20
2005 30
2006 40
2007 50
2008 60

I appologize, unfortunately right after I sent in that post I
realized I didn't include some of the more complicating factors and
made my example too easy. But I came up with a solution to this and
figured I would post it since I started the discussion even if it was
the wrong one :).
SQL> Select a.Year, a.Col1,Sum(b.Col1)
2 From foo a, foo b
3 Where a.year >= b.year
4 Group by a.Year, a.Col1
5 Order by 1, 2;

YEAR COL1 SUM(B.COL1)
--------- --------- -----------
2003 10 10
2004 10 20
2005 10 30
2006 10 40
2007 10 50
2008 10 60

6 rows selected.