# Calculate Average Balance

I am looking for a way to calculate a average balance based on daily balances, or a better suggestion would be fine.
I have a balance field in my table and the date field, is there a way where i can add balance field for each month from the 1st to end of the month and then divide it by the number of days to get a accurate average balance?

any help would be really appreciated!!!!

###### Who is Participating?

Commented:
select datepart(mm, datefield) as month, datepart(yy, datefield) as year, avg(balance) as avgs
from tablename
group by datepart(mm, datefield) , datepart(yy, datefield)
0

Commented:
select datepart(mm, datefield) as month, datepart(yy, datefield) as year, avg(balance)
from tablename
group by datepart(mm, datefield) , datepart(yy, datefield)
0

Author Commented:
Thanks, this gives me the balance of the account, not the average balance...

0

Commented:
select datepart(mm, datefield) as month, datepart(yy, datefield) as year, avg(balance) as avgs
from tablename
group by datepart(mm, datefield) , datepart(yy, datefield)
compute avg(avgs)
0

Commented:
Im pretty sure it gives you the avg balance for the time period....
0

Author Commented:
Ritesh, its throwing this error

Msg 207, Level 16, State 1, Line 4
Invalid column name 'avgs'.
Msg 411, Level 16, State 1, Line 4
COMPUTE clause #1, aggregate expression #1 is not in the select list.

0

Commented:
try this one:

select datepart(mm, datefield) as month, datepart(yy, datefield) as year, avg(balance) as avgs
from tablename
group by datepart(mm, datefield) , datepart(yy, datefield)
compute avg(avg(balance))
0

Author Commented:
getting same ending balance instead of average balance. is this query be modified so i can enter the starting date and ending date of the month...

0

Author Commented:
Thank you all...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.