U! How to calculate column average

From: Coca <bigmelon_at_btamail.net.cn>
Date: Thu, 12 Feb 2004 23:05:46 +0800
Message-ID: <bh5n205141hv5obiahuqj841o9v4nncujt@4ax.com>

Hi, I am working on a project using Oracle 9i.

table A Fields: Account

AccKey number(18),
AccName varchar2(30)

table B Fields: AccFact

sDate Date
AccKey number(18),
AccValue number(25,2).

---

Records of table A:

AccKey AccName
1001 Richard
1002 Mike
1003 Mary
1004 Hellen
...

Records of table B:

sDate AccKey AccValue

2004/1/1 1001 100.00
2004/1/1 1002 150.00
2004/1/2 1001 300.00
2004/1/2 1002 350.00
2004/1/2 1003 600.00

...

I would like to have a resultset like this:

"sDate" Richard Mike Mary AverageValue

2004/1/1 100.00 150.00 null (100+150)/2, since Mary's value is

null, it is not accounted in the average 2004/1/2 300.00 350.00 600.00 (300+350+600)/3

End-users choose which account name to be shown, so the resultset's columns is not

fixed.

I have used SUM(DECODE(Acckey,1001,AccValue,NULL)) AS
"Richar" etc to generate the

following resultset:
"sDate" Richard Mike Mary

2004/1/1 100.00 150.00 null
2004/1/2 300.00 350.00 600.00

But I don't know how can I generate the column Average?

Any tips are appreciated. Received on Thu Feb 12 2004 - 09:05:46 CST