0

I'm using sql server 2012, where I have table [BonusTransactions] 

(Id, Author, Bonus)
1    Andrew  5 
2    Andrew  2
3    Jim     6
4    Jim     2
5    Jim     15

I need to make query that will return this table with cumulative field for every Author:

(Id, Author, Bonus, TotalBonus)
1    Andrew  5      5  (5)
2    Andrew  2      7  (5+2)
3    Jim     6      6  (6)
4    Jim     2      8  (6 + 2)
5    Jim     15     23 (6 + 2 + 15)

So I tried this query:

SELECT  Id, Author, Bonus,
Sum(Bonus) Over (Partition By Author order by Id) as TotalBonus
FROM [dbo].[BonusTransactions]

but it doesn't work: "Incorrect syntax near 'order'."

Also i tried this:

SELECT  Id, Author, Bonus,
Sum(Bonus) Over (Partition By Author order by Id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as TotalBonus
FROM [dbo].[BonusTransactions]

But in this case intellisense highlights "ROWS BETWEEN" and column names

Alexander Pidoprigora
  • 61
  • 1
  • 7
  • Check your compatibility level. You may be using SQL Server 2012 but with compatibility set to an earlier version. Here is a place to start: https://msdn.microsoft.com/en-us/library/bb510680.aspx. – Gordon Linoff Oct 28 '15 at 11:11
  • 1
    See this thread http://stackoverflow.com/questions/2120544/how-to-get-cumulative-sum. It has solutions without window functions. – Amir Pelled Oct 28 '15 at 11:13

1 Answers1

0

Your problem is probably the compatibility level. Either set it to 110 for SQL Server 2012, or use a construct available in earlier versions. Here is one method:

SELECT t.Id, t.Author, t.Bonus, tt.TotalBonus
FROM [dbo].[BonusTransactions] t outer apply
     (SELECT Sum(t2.Bonus) as TotalBonus
      FROM [dbo].[BonusTransactions] t2
      WHERE t2.Author = t.Author AND t2.Id <= t.id
     ) tt;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786