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!!!!

fahVBAsked:
Who is Participating?
 
RiteshShahConnect With a Mentor Commented:
select datepart(mm, datefield) as month, datepart(yy, datefield) as year, avg(balance) as avgs
from tablename
where datefield>='your start date' and datefield<='your end date'
group by datepart(mm, datefield) , datepart(yy, datefield)
0
 
chapmandewCommented:
select datepart(mm, datefield) as month, datepart(yy, datefield) as year, avg(balance)
from tablename
group by datepart(mm, datefield) , datepart(yy, datefield)
0
 
fahVBAuthor Commented:
Thanks, this gives me the balance of the account, not the average balance...

0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
RiteshShahCommented:
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
 
chapmandewConnect With a Mentor Commented:
Im pretty sure it gives you the avg balance for the time period....
0
 
fahVBAuthor 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
 
RiteshShahCommented:
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
 
fahVBAuthor 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
 
fahVBAuthor 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.

All Courses

From novice to tech pro — start learning today.