1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

how to calculate the average balance???

Discussion in 'SQL PL/SQL' started by neptune, Apr 8, 2011.

  1. neptune

    neptune Guest

    hi all ,

    I have the following table "account_details"

    account balance transaction_date
    1 100 1/1/2011
    1 200 5/1/2011
    1 -100 20/1/2011
    1 500 31/1/2011
    1 200 3/3/2011
    2 100 6/2/2011

    account -> account number
    balance -> the balance for that account
    transaction_date -> date of transaction made.

    I am required to calculate the average balance for every account for every month of the current year considering only the positive balances only. Phew! trying to make it simpler....

    ex: account =1
    avg balance ( for jan )= [ 100 * (5-1+1) + 200 * (31-5+11) + 500 * (31-31+1) ] /31
    (please see that -100 has been ignored )

    avg balance (for feb) = [500 * 28 ] / 28

    i need to display the account number, the month and year and the average balance for that month.... [for all the previous 12 months from todays date]


    Thanks in advance for all the geniuses out there :)

    P.S i am using oracle 10 g
     
  2. debasisdas

    debasisdas Active Member

    Messages:
    46
    Likes Received:
    3
    Trophy Points:
    90
    Location:
    Bangalore, India
    Lets see your effort, What is the code that you are working on ?

    When you say average, is it per day or per total number of transaction ?

    Per day calculation does not make any sense.
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This should work for you; I really don't understand your calculations unless you're computing an average daily balance (which isn't the result you state in your problem definition):

    Code (SQL):
    SQL> CREATE TABLE acct_info(
      2          account NUMBER,
      3          acct_bal NUMBER,
      4          txn_date DATE);
     
    TABLE created.
     
    SQL>
    SQL> INSERT ALL
      2  INTO acct_info
      3  VALUES(1,100,to_date('01/01/2011', 'DD/MM/RRRR'))
      4  INTO acct_info
      5  VALUES(1,200,to_date('05/01/2011','DD/MM/RRRR'))
      6  INTO acct_info
      7  VALUES(1,-100,to_date('20/01/2011','DD/MM/RRRR'))
      8  INTO acct_info
      9  VALUES(1,500,to_date('31/01/2011','DD/MM/RRRR'))
     10  INTO acct_info
     11  VALUES(1,200,to_date('03/03/2011','DD/MM/RRRR'))
     12  INTO acct_info
     13  VALUES(2,100,to_date('06/02/2011','DD/MM/RRRR'))
     14  SELECT * FROM dual;
     
    6 ROWS created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> SELECT account, to_char(txn_date, 'MM/RRRR') mth, round(avg(acct_bal), 2) avg_bal
      2  FROM
      3  (SELECT account, txn_date, acct_bal
      4  FROM acct_info
      5  WHERE acct_bal > 0)
      6  GROUP BY account, to_char(txn_date, 'MM/RRRR')
      7  ORDER BY 1, 2
      8  /
     
       ACCOUNT MTH        AVG_BAL
    ---------- ------- ----------
             1 01/2011     266.67
             1 03/2011        200
             2 02/2011        100
     
    SQL>