Sum and averaging question

[Queryman]

I have a table with the following values
orig_acct,
alt_acct,
value1,
value2,
value3

Orig_acct is unique but alt_acct is not. I need to sum and get the average of the values of value1, value2, value3 for each orig_acct. So for each orig_acct there are many values and in each case it's different. Can this be done in one query?
Thanks!



Michael

 

[tdatgod]

Hi,
I guess I don't understand the description.

If Orig_acct is UNIQUE then their can only be one row in the table with that information. if that is the case then this should work.

sel orig_acct,
(value1 + value2 + value3) as summation,
(summation / 3 ) as value_average
from table;

 

[Queryman]

I am sorry, I did not explain my sitaution correctly. The table has five values
orig_acct,
alt_acct,
value1,
value2,
value3

There are numerous alt_acct's for each orig_acct, so there are no unique rows in the table.

What I am trying to do is trying to add three more columns to my table called avg_value1, avg_value2, avg_value3 and populate it with the average of value1,2, 3 etc for each orig_acct. I can see I need to make a new table with just
the following columns
Orig_acct,
value1,
value2,
value3

I also can get a count of the alt_accts in the original table by doing a count(*) for orig_acct

My real question is how do I update the avg values in the new table so that it has sum(value1) from the 1st table divided by count(*) value to get the average etc

Thanks so much



Michael

 

[Queryman]

I figured this out myself. Thanks!



Michael

 

[dnoeth]

You can calculate that data on the fly using OLAP functions:

select
orig_acct,
alt_acct,
value1,
value2,
value3,
avg(value1) over (partition by orig_acct
rows between unbounded preceding
and unbounded following) as avg_value1,
avg(value2) over (partition by orig_acct
rows between unbounded preceding
and unbounded following) as avg_value2,
avg(value3) over (partition by orig_acct
rows between unbounded preceding
and unbounded following) as avg_value3
from table

So just insert/select that into a new table.
But the real question is:
Do you really have to store that data in extra columns? It's redundant, when you update a value, you'll have to recalculate the average again. Why don't you just put the query above in a view.

Dieter

 

[Queryman]

Thanks Dieter,
This is not a production job, just ad-hoc, so I delete all the tables created at the end of job anyway.
Thanks for the info though...



Michael