Home » RDBMS Server » Server Administration » SQL accumulating summarized values
SQL accumulating summarized values [message #370283] Mon, 26 July 1999 15:08 Go to next message
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 Go to previous message
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
Previous Topic: How to read from file in PL/SQL
Next Topic: Multiple Values
Goto Forum:
  


Current Time: Wed Oct 28 06:45:47 CDT 2020