Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2011
    Posts
    2

    Talking Unanswered: find the average balance

    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. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    any chance you could reproduce the actual assignment question verbatim?

    the reason i ask is because "for every month of the current year" and "for all the previous 12 months from todays date" are two completely different queries, and homework assignments aren't usually that vague

    also, what have you tried so far?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2011
    Posts
    1

    Analytical Functions.....

    As I agree w/ the previous poster, that this is almost certainly a homework problem, I'll just point you in the right direction.

    You should look at analytical functions. If you still have questions or problems, post here with examples of what you tried, and what's not working, and I'm sure you'll be able to get some assistance.

    -Mark

Posting Permissions

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