Re: How would I write...? HELP!

From: Peter Rak <po080016_at_pop.ke.sanet.sk>
Date: 1996/06/21
Message-ID: <31CAB97E.6AE2_at_pop.ke.sanet.sk>#1/1

Mike Oswald wrote:
>
> I am working with a Cytogenetics group doing chromosome studies/research
> and they have a need for controls (control data). One of their tables
> might look like...
>
> signal_1 signal_2 signal_3 signal_4 signal_1_pct signal_2_pct ..etc..
> -------- -------- -------- -------- ------------ ------------
> 12 78 10 0 12% 78%
> 5 75 18 2 8.5% 76.5%
> 6 84 10 0 7.76% 79%
>
> I need to SUM each signal and divide by the total cell count for each record
> to get the percentage for each signal.
>
> I know that I can/might be able to do this within the SQL*Forms at the time
> each record is commited to the database (the researcher decides which of the
> data records will be added to the control set).
>
> Anyone have a clue as to how I might process this information? Using the
> SUM feature groups data and I want to process one record at a time but be
> able get the TOTAL cell count and the TOTAL SIGNAL_n count too.
>
> That's in advance for your help...
>
> -Mike

Hi,

I hope, in your table exists another one column for 'order by' clause (some sequence or date). If it is true then try following:

create table x(d date,x1 number,x2 number,x3 number,x4 number);

insert into x values(trunc(sysdate),12,78,10,0);
insert into x values(trunc(sysdate)+1,5,75,18,2);
insert into x values(trunc(sysdate)+2,6,84,10,0);
...
...
...
...

...
...
commit;

col x1 for 90.99
col x2 for 90.99
col x3 for 90.99
col x4 for 90.99
col s1 for 90.99
col s2 for 90.99
col s3 for 90.99
col s4 for 90.99

select a.d

,a.x1
,a.x2
,a.x3
,a.x4
,100*sum(b.x1)/sum(b.x1+b.x2+b.x3+b.x4) s1
,100*sum(b.x2)/sum(b.x1+b.x2+b.x3+b.x4) s2
,100*sum(b.x3)/sum(b.x1+b.x2+b.x3+b.x4) s3
,100*sum(b.x4)/sum(b.x1+b.x2+b.x3+b.x4) s4

from x a,x b
where b.d <= a.d
group by a.d

,a.x1
,a.x2
,a.x3
,a.x4

order by 1
/

You have to use nvl() function, if you have somewhere null values. Please, re-mail after trying...

                Peter. Received on Fri Jun 21 1996 - 00:00:00 CEST