Dividing field values for the average 3

[darlet]

I have the numeric field A in Table CALC.
I would like to add a column B in which each
value is A/AVG(A).....

A B
0.91 1.005079717
0.48 0.528822191
1.37 1.50921702
1.02 1.122162345
0.86 0.954474088
0.35 0.385052138
0.88 0.967094062
1.17 1.288388365
1.12 1.239710074

Thanks for your help!!

 

[r937]

select A, avg(A) as B from CALC group by A

r937.com ;-)rudy.ca

 

[darlet]

Hi rudy...thanks for your tip...
if I have also a C column and I desire to get a D column
as C/AVG(C) I should query

select A,(A/avg(A)) as B, C, (C/avg(C)) as D group by A,C ???

I don't have Oracle at home...just my curiosity till monday
morning!

 

[darlet]

Well I just checked with access,

select A, avg(A) as B from CALC group by A

A MedA
1 1
4 4
5 5
34 34
53 53
99 99

doesn't seem to work....

When i group by A then AVG(A) = A ....

 

[r937]

i guess i don't really understand what you want

try this --

select A
     , A / (select avg(A) from CALC) as B
  from CALC

r937.com | rudy.ca

 

[darlet]

Thanks rudy..that's exactly what I wanted.

 

[darlet]

Well...just a little bit further...

The CALC table I was talking about is actually the result
of a quite complex query.

I've just tried this:

select A
     , A / (select avg(A) from CALC) as B
  from 

(Select .....) CALC

Oracle highlights the CALC in (select avg(A) from CALC)
and tells me ORA-00942: table or view does not exist....

Any suggestion?

 

[PHV]

Either create a view called CALC or use this:
select A
, A / (select avg(A) from (Select .....) CALC) as B
from
(Select .....) CALC

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[darlet]

Thanks PHV. It works. I guess that if iI have also a C column and I desire to get a D column
as C/AVG(C) I should query

select A
     , A / (select avg(A) from (Select .....) CALC) as B
     , C / (select avg(C) from (Select .....) CALC) as D
  from 
(Select .....) CALC

Probably creating first the view will optimize the processing time(I suppose that the previous query triples
it, generating the same data 3 times - the Select .....) CALC query - ). Is there a way to create the view and immediately after querying it? (I mean inside the same script...something like:

create view CALC as 
  select... 
   ... ;

select A
     , A / (select avg(A) from CALC) as B
     , C / (select avg(C) from CALC) as D
from CALC;

I'm asking this because I will schedule the entire process
and if I can schedule the view creation and the query within the same script that would be nicer!

 

[PHV]

Have a look at stored procedure.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dnoeth]

Hi darlet,
you mentioned Oracle, so you can probably use an Analytic Function, check the manuals for details:

select A
     , A / (avg(A) over ()) as B
     , C / (avg(C) over ()) as D
  from
(Select .....) CALC

"avg(A) over ()" is equal to
"avg(A) over (rows between unbounded preceding and unbounded following)"

Dieter

 

[darlet]

Thank you Dieter, it works!!!!!

D.