Xref: alice comp.databases.oracle.misc:18395
Path: alice!news-feed.fnsi.net!newsfeed.wli.net!su-news-hub1.bbnplanet.com!su-news-feed1.bbnplanet.com!news.bbnplanet.com!inet16.us.oracle.com!not-for-mail
From: tkyte@us.oracle.com (Thomas Kyte)
Newsgroups: comp.databases.oracle.misc
Subject: Re: PL/SQL question
Date: Sat, 08 Aug 1998 13:55:04 GMT
Organization: Oracle Government
Lines: 93
Message-ID: <35cc5733.1285638@192.86.155.100>
References: <1998080721583400.RAA15046@ladder03.news.aol.com>
Reply-To: tkyte@us.oracle.com
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Newsreader: Forte Agent 1.5/32.451

A copy of this was sent to iancrozier@aol.com (Iancrozier)
(if that email address didn't require changing)
On 7 Aug 1998 21:58:34 GMT, you wrote:

>I have a table which includes four columns: A,B,C and D
>I need to create a report which will give me the average value of
>B for every unique value of A. That's easy.
>I also need to put in the report the ratio of the sum of all C's for a
>particular value
>of A to the the sum of all the values of C when D is equal to 1.
>I don't see any way to do it in SQLPLUS because we need to query the same table
>in two different ways. So, it looks like I need to use PL/SQL.
>

I don't know what 'the ratio of the sum of all C's for a particular value'
means.  Guessing at what it means you could try:

select t1.a, t1.avg_b, t2.sum_c 
from ( select A, avg(B) avg_b from T group by A ) t1, 
     ( select A, sum(C) sum_c from T where D = 1 group by A ) t1
where t1.a = t2.a (+)

or even:

select A, avg(B) avg_b, sum( decode( D, 1, C, 0 ) ) sum_c
  from T
 group by A
/

they will give you the same answer...  Perhaps you can elaborate on what result
set you are trying to get with an example of table data and required output.
You can probably do it in one query with an inline view or by using decode.


>Questions:
>
>1. Is is possible to create an array in PL/SQL e.g.
>
>v_sum_C(n)       number(6,3);
>

Yes, 

declare
    type myArray is table of number index by binary_integer;
    v_sum_C    myArray;
begin
    ....
end;

>(where n can range from 1 to whatever I need)
>so I can feed different values of "select sum(C) from table group by A"  into
>one "variable array" instead of declaring a multitude of different variables.
>Then, can I 
>put those values into a temporary table where I can then do the necessary
>calculation for the report.
>
>2. Can I feed the results of e.g.
>
>select sum(C) from table where D = 1 group by A
>
>directly into a temporary table?
> 

No, no temporary tables.  You can put it into a pl/sql table as above in the
following manner:

   for x in ( select sum(c) sum_c from table where d = 1 group by a )
   loop
       v_sum_c( v_sum_c.count+1 ) := x.sum_c;
   end loop;

>Something like:
>select sum(C) from table where D = 1 group by A into table2.sumc
>
>3. If I can't do either 1 or 2, how can I do this?
>
>Thanks in advance

 
Thomas Kyte
tkyte@us.oracle.com
Oracle Government
Herndon VA
 
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation
 
Anti-Anti Spam Msg: if you want an answer emailed to you, 
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.