# Thread: find the average balance

1. Registered User
Join Date
Apr 2011
Posts
2

## 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. SQL Consultant
Join Date
Apr 2002
Location
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?

3. Registered User
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
•