# Thread: PL/SQL for calculate the Geometric Mean of data column!

1. Registered User
Join Date
Dec 2005
Posts
59

## Unanswered: PL/SQL for calculate the Geometric Mean of data column!

Hello,

I would like to calculate the geometric mean of column data based on monthly recorded_date as following:

Here is Geometric Mean Formula :
Geometric Mean = ((X1)(X2)(X3)........(XN))^1/N
where ^ stand for exponent.

I'm newbie and totally not sure how to code PL/SQL Oracle for geometric mean aggregate function that can solve above issue.

My detailed column data as shown below:

Test | Value |Recorded_date
-----------------------------
1 | 7.8 | 3/1/2012
1 |100.3 | 3/15/2012
1 |48.9 | 3/30/2012
1 |98.25 | 3/31/2012
2 |58.3 | 3/2/2012
2 |99.0 | 3/14/2012
2 |45.6 | 3/19/2012
2 |99.2 | 3/25/2012
2 |100.4 | 3/26/2012
2 |78.98 | 3/28/2012
2 |98.99 | 3/29/2012
2 |66.66 |3/30/2012
2 | 88.77 |3/31/2012

The expected result

Test | GEO_MEAN(Value)
=====================
1 | ?
2 | ?

I really appreciate your time to work out on my issue. Thanks in advance.
Last edited by avt2k6; 04-01-12 at 12:50.

2. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
Try the follwing expression to calculate Geometric Mean in Oracle SQL.
POWER( 10 , AVG( LOG(10 , value) ) )

Explanation:
Geometric Mean = ((X1)(X2)(X3)........(XN))^1/N
LOG(base, Geometric Mean) = ( LOG(base, X1) + LOG(base, X2) + LOG(base, X3) + ....... + LOG(base, XN) ) / N
LOG(base, Geometric Mean) = AVG( LOG(base, Xn) ) : Where n = 1, 2, 3, ... , N
POWER(base, LOG(base, Geometric Mean) ) = POWER(base, AVG( LOG(base, Xn) ) ) : Where n = 1, 2, 3, ... , N
Geometric Mean = POWER(base, AVG( LOG(base, Xn) ) ) : Where n = 1, 2, 3, ... , N
Last edited by tonkuma; 04-01-12 at 14:06. Reason: Add Explanation.

3. Registered User
Join Date
Dec 2005
Posts
59