Solved

# Calculate Average Balance

Posted on 2009-04-27
935 Views
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!!!!

0
Question by:fahVB

LVL 60

Expert Comment

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 Comment

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

0

LVL 31

Expert Comment

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

LVL 60

Assisted Solution

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

Author Comment

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

LVL 31

Expert Comment

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 Comment

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

LVL 31

Accepted Solution

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

Author Closing Comment

Thank you all...
0

## Featured Post

### Suggested Solutions

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade toolâ€¦
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.