Re: Query Help

From: Romeo Olympia <rolympia_at_hotmail.com>
Date: 18 May 2004 21:59:27 -0700
Message-ID: <42fc55dc.0405182059.585c9f7_at_posting.google.com>


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

mackdaddy315_at_hotmail.com (DM) wrote in message news:<bf2880e.0405180921.20de01f_at_posting.google.com>...
> 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
Received on Wed May 19 2004 - 06:59:27 CEST

Original text of this message