Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2005
    Posts
    59
    Provided Answers: 1

    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

    Please help your idea how write SQL to calculate Geometric Mean of column based on another column condition. Thanks in advance.
    Last edited by avt2k6; 05-16-12 at 11:05.

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    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/
    Please, do consult it.

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    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. #4
    Join Date
    Dec 2005
    Posts
    59
    Provided Answers: 1
    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. #5
    Join Date
    Dec 2005
    Posts
    59
    Provided Answers: 1
    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. #6
    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:
    consider your first sample data
    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
  •