Re: COMPUTE SUM CALCULATIONS!!! URGENT

From: alexjunq <alexjunqNOalSPAM_at_softwaredesign.com.br.invalid>
Date: Wed, 08 Mar 2000 20:35:56 -0800
Message-ID: <0505bc48.fe3f8586_at_usw-ex0103-024.remarq.com>

Hi,

  u can't do this directly, because u can't define functions   for compute...

  instead of, you could use an similar aproach, using another   calculation column, that gives u similar report (with one   extented column) but with desired data.

  take a look in this example:

/* sample data */

create table x (
  p number,
  a number,
  b number
)
/

insert into x values (2, 10, 10);
insert into x values (3, 10, 10);
insert into x values (3,  0,  5);
insert into x values (4, 10,  5);
insert into x values (1,  5, 10);
insert into x values (1,  5, 10);

commit ;

/* report */

break on report

column sa format 9999 heading "SUM(A)"
column sb format 9999 heading "SUM(B)"
column pct format 9999 heading "% B/A"

column pct2 format 9999 heading "% total"

clear computes
compute sum of sa on report
compute sum of sb on report
compute sum of pct2 on report

select p,

       sum(a) sa,
       sum(b) sb,
       decode (sign(sum(a)),
               0,sum(b)*100,
              ((sum(b)-sum(a))/sum(a)*100)) pct,
       decode (sign(sum(a)),
               0, decode (sign(max(y.n)),
                          0, sum(b)*100,
                          sum(b)/max(y.n)*100),
               ((sum(b)-sum(a))/max(y.n)*100)) pct2
from   (select sum(a) n
        from   x) y,
       x

group by p
/

/* results */

        P SUM(A) SUM(B) % B/A % total
--------- ------ ------ ----- -------

        1     10     20   100      25
        2     10     10     0       0
        3     10     15    50      13
        4     10      5   -50     -13
          ------ ------       -------
sum           40     50            25

/* finish */

  but be aware that subselect will be an second   full/range scan in your source table... this could   be dangerous if you're working with big amount of data.

  Hope it helps.

  Alexandre Junqueira

• Sent from RemarQ http://www.remarq.com The Internet's Discussion Network * The fastest and easiest way to search and participate in Usenet - Free!

Received on Thu Mar 09 2000 - 05:35:56 CET