# Thread: Calculate exp(avg(ln(value) as GEOMETRIC_MEAN based on Description column condition!

1. Registered User
Join Date
Dec 2005
Posts
59

## Unanswered: Calculate exp(avg(ln(value) as GEOMETRIC_MEAN based on Description column condition!

Hello all,

I have an Oracle table consists two columns
value | Description
-------------------
1000 | good
2000 | n/a
3000 | excellent

If the description column is n/a, the value must set equal 0 before calculating the GEOMETRIC MEAN.

I used the following query, but it displayed the error: not a GROUP BY expression

select (CASE Description = 'N/A' Then 0.0 ELSE exp(avg(ln(value)) END)
from sample_table

Last edited by avt2k6; 05-16-12 at 11:05.

2. Registered User
Join Date
Mar 2007
Posts
629
Hello,
what about using correct syntax of CASE expression? At the first look, WHEN word is missing.

Fortunately, Oracle provides its description in SQL Language Reference book, which are available with other Oracle documentation e.g. online on http://tahiti.oracle.com/

3. Registered User
Join Date
Mar 2002
Location
Posts
1,137
You need the case inside the avg() I think i.e.

select exp(avg(case when Description = 'N/A' Then 0 else ln(value) end))
...

Alan

4. Registered User
Join Date
Dec 2005
Posts
59
Hello all,

Thank you for your quickly response. I am sorry when missing WHEN typo.
I used the case inside the avg() and the query is working well

select exp(avg(case when Description = 'N/A' Then 0 else ln(value) end))

and the result is equal 1 because exp(0) = 1, but I expect GEOMEAN = 0.

Let me clear my problem is as following:

Formula GEOMEAN = exp(avg(ln(value)))

I have an table consists of the columns:

Case 1:
value | Description
-------------------
0.7 | n/a

I would like the result GEOMEAN = exp(avg(ln(value))) = 0

Case 2:

value | Description
-------------------
9.6 | good
30 | excelent

The result is GEOMEAN = exp(avg(ln(value)))

In a brief, query in blue color without error when running, but the red color is displayed the error: not a GROUP BY expression

SELECT item, exp(avg(CASE WHEN Description = 'N/A' Then 0 ELSE ln(value) END))
FROM sample_table
GROUP BY item;

SELECT item, (CASE WHEN Description = 'N/A' Then 0 ELSE exp(avg(ln(value))) END)
FROM sample_table
GROUP BY item;

Any further work around help is much appreciated.
Last edited by avt2k6; 05-16-12 at 12:52.

5. Registered User
Join Date
Dec 2005
Posts
59
I found the way to work around to get GEOMEAN = 0 when Description = 'n/a'

SELECT item, ROUND(EXP(AVG(CASE WHEN Description = 'N/A' THEN ln (0.1) ELSE LN (value) END )))
FROM SAMPLE_TABLE
GROUP BY item;

In math, LN X where X > 0, so I cannot use LN 0. Therefore, I picked the round function with LN (0.1) or whatever LN (0.00001).

6. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
My question is how do you want to treat rows with Description = 'n/a'?

For example:
Code:
```value | Description
-------------------
1000 | good
2000 | n/a
3000 | excellent```
which result do you want?
(1) EXP( ( LN(1000) + 0 + LN(3000) ) / 3 ) = 144
(2) EXP( ( LN(1000) + LN(3000) ) / 2 ) = 1732

If you want (1)
the expression might be
EXP(AVG( CASE WHEN Description = 'n/a' THEN 0 /* = LN(1) */ ELSE LN(value) END ))
* SIGN(SUM( CASE WHEN Description = 'n/a' THEN 0 ELSE 1 END ))

Note: WHEN Description = 'n/a' THEN 0 /* = LN(1) */
LN(0.1) -2.3025 and LN(0.00001) = -11.5129
So, I thought LN(1) = 0 might be better.

If you want (2)
the expression might be
NVL( EXP(AVG( CASE WHEN Description = 'n/a' THEN null ELSE LN(value) END )) , 0 )

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•