| SQL accumulating summarized values [message #370283] |
Mon, 26 July 1999 15:08  |
Luis Claudio
Messages: 5
Registered: July 1999
|
Junior Member |
|
|
Hi folks,
I've a big problem. I want that my summarized query returns accumulative values, i.e. :
- group by year :
select year, sum(quantity)
from product
group by year
year sum(quantity)
----------------------
1997 100
1998 50
1999 85
but I want grouped data by year with accumulative values :
year sum(quantity)
----------------------
1997 100 (100 + 0)
1998 150 (100 + 50)
1999 235 (150 + 85)
how is this SELECT statament built?
Thanx in advance,
Luis Claudio
|
|
|
|
| Re: SQL accumulating summarized values [message #370284 is a reply to message #370283] |
Mon, 26 July 1999 16:00  |
hmg
Messages: 40
Registered: March 1999
|
Member |
|
|
My solution is to use a stored function. Look at the script below please
drop table product;
create table product (
year number(4),
quantity number(10)
);
insert into product (year, quantity) values (1997,100);
insert into product (year, quantity) values (1997,50);
insert into product (year, quantity) values (1998,30);
insert into product (year, quantity) values (1998,40);
insert into product (year, quantity) values (1999,120);
insert into product (year, quantity) values (1999,80);
create or replace function getsum(year_in in product.year%type)
return product.quantity%type
is
cursor c is
select sum(quantity) runningtotal from product
where year <= YEAR_IN;
v_sum product.quantity%type;
begin
open c;
fetch c into v_sum;
close c;
return ( v_sum );
exception
when others then
return (null);
end;
/
show errors
select year, getsum(year) from product
group by year;
/* Output
YEAR GETSUM(YEAR)
---------- ------------
1997 150
1998 220
1999 420
*/
Bye HMG
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
