Re: Query Help
From: DM <mackdaddy315_at_hotmail.com>
Date: 19 May 2004 08:19:04 -0700
Message-ID: <bf2880e.0405190719.2c62b14c_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
Date: 19 May 2004 08:19:04 -0700
Message-ID: <bf2880e.0405190719.2c62b14c_at_posting.google.com>
> 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
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. Received on Wed May 19 2004 - 17:19:04 CEST