| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: SQL: Inner Join ??
On 17 Nov 1998 22:47:00 GMT, noodles_at_aol.com (Noodles) wrote:
>I'm trying to do an aggregate, SUM, on a column while also selecting each value
>included in the SUM grouping. For Example:
>
>A B C
>-- -- --
>10 20 1
>10 21 2
>10 22 1
>11 23 1
>11 24 1
>
>
>I would like an output each B value along with C's "B" value divided by SUM(C)
>grouped by A. Basically it's a distribution of each C value over the sum of
>the C values within each unique A
>
>ie
> 20 0.25 where 0.25 = 1/(1+2+1)
> 21 0.50 where 0.50 = 2/(1+2+1)
> 22 0.25 where 0.25 = 1/(1+2+1)
> 23 0.50 where 0.50 = 1/(1+1)
> 24 0.50 where 0.50 = 1/(1+1)
>
>
>I can get parts of this to work but I need 2 levels of access: One; for the SUM
>of C grouped by A, Two; A row-level select within each group ID. I think an
>inner or self-join is the correct path but I can't get the syntax right.
>
>Is SQL the right way because I think I might a looping construct from PL/SQL ?
>I'm leaning towards SQL because ultimately I plan to use this SQL in a view.
>
>Can anybody please point me in the right direction ?
Try this...
SQL> select * from bar;
A B C
---------- ---------- ----------
10 20 1
10 21 2
10 22 1
11 23 1
11 24 1
SQL>
1 select x.b, c/y.s
2 from bar x,
3 ( select a, sum( c ) s from bar group by a ) y
4* where x.a = y.a;
B 1/Y.S
---------- ----------
20 .25
21 .5
22 .25
23 .5
24 .5
Hope this helps.
chris.
>
>TIA,
>Cliff
>(noodles_at_aol.com)
>(clifford_buetikofer_at_merck.com)
>
>
>
>
--
Chirstopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.
 |
 |