Ordering results over partition in aggregate function

Question

I'm studying T-SQL set operators and Window functions. I'm following a video lecture on Microsoft Virtual Academy but I'm encountering a syntax error I can't solve.

I have a dummy Transactions table:

CREATE TABLE [dbo].[Transactions]
(
    [TransactionID] [int] IDENTITY(1,1) NOT NULL,
    [AccountID] [int] NOT NULL,
    [Amount] [money] NOT NULL,
    [TransactionDate] [datetime] NOT NULL
)

that I filled with some random data:

INSERT [dbo].[Transactions] ([AccountID], [Amount], [TransactionDate]) 
VALUES (1, 20.5000, '2018-07-03'), (1, 19.9900, '2018-07-03'),
       (2, 65.3000, '2018-07-03'), (2, 99.0000, '2018-07-04'),
       (1, 13.3000, '2018-07-04'), (1, 12.9900, '2018-07-04'),
       (2, 37.2500, '2018-07-04'), (1, 50.0000, '2018-07-05'),
       (1, 17.3500, '2018-07-05'), (2, 24.9000, '2018-07-05'),
       (1, 11.2500, '2018-07-05'), (2, 39.9000, '2018-07-05')

What I want now (following the lecture) is showing all the columns plus an additional column CurrentBalance, showing the balance after the amount is added to the previous amounts, like this:

TransactionID AccountID   Amount                TransactionDate         Balance
------------- ----------- --------------------- ----------------------- -----------
1             1           20,50                 2018-07-03 00:00:00.000 20,50
2             1           19,99                 2018-07-03 00:00:00.000 40,49
5             1           13,30                 2018-07-04 00:00:00.000 53,79
6             1           12,99                 2018-07-04 00:00:00.000 66,78
.
.
.

According to the lecture and the documentation on MSDN, the following query should do

SELECT
    *,
    SUM(Amount) OVER(PARTITION BY AccountID ORDER BY TransactionID, AccountID) AS Balance
FROM 
    Transactions
ORDER BY 
    TransactionID, AccountID

but I'm shown this error message:

Messaggio 102, livello 15, stato 1, riga 6
Incorrect syntax near 'order'.

As for the comments stating this is not the code I get the error from, here is a screenshot

I'm using SSMS v17.6 on a remote SQL Server 2008 R2.

Thanks, Davide.

He meant, is this the complete code or have you missed something to provide here. Because the snippet looks good — Abhishek, Jul 05 '18 at 13:05
I meant there is no relation between an error message (you provided) & your query. — Yogesh Sharma, Jul 05 '18 at 13:06
Tested on Sql server 14 and 17 with your data and your statement works fine. I don't have a version 12 to test but it should be ok too... your issue is probably elsewhere like others said; Check it here http://sqlfiddle.com/#!18/8dda4/1 and there http://rextester.com/live/MQSUNX46347 — Thomas G, Jul 05 '18 at 13:10
@ThomasG Thanks for checking. Though, I realized I wrote the wrong version, is 2008R2. Corrected now — Davide Vitali, Jul 05 '18 at 13:13
It works only if I comment out the inner ORDER BY clause (but I don't get what I need then) — Davide Vitali, Jul 05 '18 at 13:19
2008 doesnt support OVER...ORDER BY on an aggregate (SUM here). Check [answers here](https://stackoverflow.com/questions/19684249/tsql-2008-sumx-over-partition-order-by-clause) for an alternative — Thomas G, Jul 05 '18 at 13:20
this is a duplicate of https://stackoverflow.com/questions/19684249/tsql-2008-sumx-over-partition-order-by-clause — Jodrell, Jul 05 '18 at 13:28

Ordering results over partition in aggregate function

0 Answers0