2

Not sure how to word this. Say i have a select returing this.

Name, month, amount
John, June, 5
John, July,6
John, July, 3
John August, 10

and I want to aggregate and report beggining blance for each month.

name, month, beggining balance.

john, may, 0
john, june, 0
john, july, 5
john, august, 14
john, September, 24

I can do this in excel with cell formulas, but how can I do it in SQL without storing values somewhere? I have another table with fiscal months i can do a left outer join with so all months are reported, just not sure how to aggregate from prior months in sql.

Hell.Bent
  • 1,667
  • 9
  • 38
  • 73
  • what rdbms are you using? sql server, mysql? – Taryn Oct 12 '12 at 09:50
  • sorry TSQL SQL Server. updated. – Hell.Bent Oct 12 '12 at 09:55
  • 2
    Which version of SQL Server? This is very important as 2012 has functionality that 2008 doesn't, and 2008 has functionality that 2005 doesn't. Or perhaps its Compact Edition? The half-cartessian-product answer that you have chosen is highly inefficient as the amount of data grows *(as stated in the answer)*. But to know the best alternative requires knowing what options you have; it involves knowing which version of SQL Server you are actually using. – MatBailie Oct 12 '12 at 10:19

3 Answers3

2
select
name
, month
, (select sum(balance) from mytable 
   where mytable.month < m.month and mytable.name = m.name) as starting_balance
from mytable m
group by name, month

This is not as nice as windowing functions, but since they vary from database to database, you'd need to tell us which system you are using.

And it's an inline subquery, which is not very performant. But at least it's easy to understand what's going on !

davek
  • 22,499
  • 9
  • 75
  • 95
  • Thank you. Need to test this out. Marking you as answer since first. Did not want to complicate the question, but what if there is business complexity - for example, I want to add a percentage of the prior month and that percentage is dictated by John's years of service with the company as of the prior month. Can I do that in a function inline? – Hell.Bent Oct 12 '12 at 10:01
  • yes, you could, but it would quickly become difficult to read and slow (since the inline SQL is executed for *each row*). I'd then look into a join or the RANK() and OVER() functions in Sql Server. See http://msdn.microsoft.com/en-us/library/ms176102.aspx for more info. – davek Oct 12 '12 at 10:10
  • It is not that this is an inline function that is the problem, SQL Server is excellent at expanding these out when generating the query plan. The problem is that it is a half cartessian product. *(Each subsequent row is joined to all the preceding rows. This means that a running total over 100 rows is actually processing 1+2+..+100 rows of data, 5050 rows in total...)* Later versions of SQL Server allow *(As @davek states)* use of the OVER clause which can significantly imporve this problem. – MatBailie Oct 12 '12 at 10:25
0

Use Grouping like this

SELECT NAME, MONTH , SUM(Balance) FROM table GROUP BY NAME, MONTH
Codesen
  • 7,724
  • 5
  • 29
  • 31
0

Assuming your months are represented as dates, this will give you the running total.

select t1.name, t1.month, sum(t2.amount)
from yourtable t1
left join yourtable t2 
     on t1.name = t2.name
     and t1.month>t2.month
group by t1.name, t1.month
podiluska
  • 50,950
  • 7
  • 98
  • 104