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

    Unanswered: Calculate AVG(columnA) based on columnB condition.

    Hello all,

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

    If the description column is n/a, the value must set equal 0 before calculating the AVG. Please help your idea how write PL/SQL to calculate a column based on another column condition. Thanks in advance.

  2. #2
    Join Date
    Jan 2012
    Posts
    84
    You don't need PL/SQL, just simple SQL query:
    Code:
    SELECT  AVG( CASE WHEN description = 'n/a'  THEN 0 ELSE value END )
    FROM table;
    or

    Code:
    SELECT  AVG( decode( description, 'n/a' ,  0 , value ) )
    FROM table;
    Last edited by kordirko; 04-26-12 at 19:33.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by avt2k6 View Post
    Hello all,

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

    If the description column is n/a, the value must set equal 0 before calculating the AVG. Please help your idea how write PL/SQL to calculate a column based on another column condition. Thanks in advance.
    Do you really want to get (a)?

    (a) (1000 + 0 + 3000 + 0) / 4 = 1000

    (b) (1000 + 3000) / 2 = 2000

  4. #4
    Join Date
    Dec 2005
    Posts
    59
    Provided Answers: 1
    Hello,

    Thank you all. I want to get the result of (a).

Posting Permissions

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