Re: Simple PL/SQL summation question

From: Enkidu Utnapishtim <utnapishtim43NOSPAM_at_hotmail.com>
Date: Fri, 19 Jul 2002 21:11:46 GMT
Message-ID: <3D388092.6050005@hotmail.com>

Roger,

Although this is not debugged, you need to do something like this:

DECLARE
   cursor c1 is

     select region_name, year, nvl(volume, 0)
     from tablename;

   r1 c1%rowtype;
   --

   brk_region_name        tablename.region_name%type;
   brk_year               tablename.year%type;
   brk_volume             tablename.volume%type;

   --
   total_volume_this_year tablename.volume%type;    accumulated_volume tablename.volume%type; BEGIN
   open c1;
   fetch c1 into brk_region_name, brk_year, brk_volume;    --
   total_volume_this_year := brk_volume;    accumulated_volume := brk_volume;
   --
   loop

     fetch c1 into r1;
     exit when c1%notfound;
     if r1.region_name = brk_region_name then
       if r1.year = brk_year then
         total_volume_this_year := total_volume_this_year + r1.volume;
         accumulated_volume := accumulated_volume + r1.volume;
       elsif r1_year = (brk_year + 1) then
         dbms_output.print_line(brk_region_name || ' ' ||
           to_char(brk_year,'9999') || ' ' ||
           to_char(brk_volume,'9999') || ' ' ||
           to_char(total_volume_this_year,'999999'));
         total_volume_this_year := brk_volume;
         accumulated_volume := accumulated_volume + r1.volume;
         brk_year = r1.year;
       else
         while brk_year < r1.year loop
           dbms_output.print_line(brk_region_name || ' ' ||
             to_char(brk_year,'9999') || ' ' ||
             '   0 ' ||
             to_char(total_volume_this_year,'999999'));
           brk_year := brk_year + 1;
         end loop;
     else
       dbms_output.print_line(brk_region_name || ' ' ||
         to_char(brk_year,'9999') || ' ' ||
         to_char(brk_volume,'9999') || ' ' ||
         to_char(total_volume_this_year,'999999'));
       brk_region_name := r1.region_name;
       brk_year := r1.year;

   end loop;

   dbms_output.print_line(brk_region_name || ' ' ||
     to_char(brk_year,'9999') || ' ' ||
     to_char(brk_volume,'9999') || ' ' ||
     to_char(total_volume_this_year,'999999'));

   close c1;
END; HTH (but no claims since I just typed it w/o debugging), Roger Crowley - DBA - LearningFramework

Roger wrote:
> Hi,
>
> I have a table containing region_name, year and volume.
> No unique key, i.e, multiple volume numbers for one year and region is
> possible. Also, NULL value for volume is possible.
>
> Example:
> USA 1993 100
> USA 1993 50
> USA 1993 140
> USA 1993 (NULL)
> USA 1993 130
> USA 1994 120
> USA 1994 100
> USA 1997 90
> USA 1998 80
> .....
>
>
> I want to summarise this in an aggregagate table, with region_name,
> year, total_volume_this_year and accumulated_volume.
>
> Example aggregate table:
> USA 1993 420 420
> USA 1994 220 640
> USA 1995 0 640
> USA 1996 0 640
> USA 1997 90 730
> USA 1998 80 810
>
>
> How do I perform the summations?
> I've tried using cursors with loops, but it seems that NULL values
> destroy the content of my variable (accumulated values are OK until a
> NULL value is found).
> Also, some years don't appear in the source table, but I would like
> them in the aggregate table (keeping the same accumulated value as the
> previous year). How can this be done?
>
> Any hints are much appreciated!
> Thanks
>
> Roger
Received on Fri Jul 19 2002 - 16:11:46 CDT