By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,502 Members | 1,878 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,502 IT Pros & Developers. It's quick & easy.

Query Help

P: n/a
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
Jul 19 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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

Jul 19 '05 #2

P: n/a
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.
Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.