CODEWITHSUNDEEP
sqlt-sqlsql-server-2012
Alexander Pidoprigora
Alexander Pidoprigora

Reputation: 61

cumulative column in sql server 2012

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

Upvotes: 0

Views: 101

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

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;

Upvotes: 0

Related Questions