sql running totals

Question

i have a table with a list of items and qty to be sold by a certain due date. I am trying to calculate a running total column that would show how many items would have been sold at a certain. below is my attempt to get the running totals but it doesn't work as I expect.

select item, due_date, qty, sum(qty) over (Order by item )
from apsplan 
Where item ='19-3102875'
order by item

I get the error:

Msg 102, Level 15, State 1, Line 44 Incorrect syntax near 'order'.

ps I am using SQL Server 2012.

Here is the alternative suggestion I received from here

SELECT
    item,
    due_date,
    qty,
    (SELECT SUM(t2.qty) FROM apsplan t2 WHERE t2.item <= t1.item and item = '196-31020-005') AS rolling_qty
FROM apsplan t1
WHERE
    item = '196-31020-005'
ORDER BY
    item

here is the result - not the running total but the over

item    due_date    qty rolling_qty
196-31020-005   2017-09-20 00:59:00.000 1.00000000  24.00000000
196-31020-005   2017-10-06 01:00:00.000 1.00000000  24.00000000
196-31020-005   2017-11-06 01:00:00.000 1.00000000  24.00000000
196-31020-005   2017-12-06 01:00:00.000 1.00000000  24.00000000
196-31020-005   2019-01-28 01:00:00.000 1.00000000  24.00000000
196-31020-005   2019-02-04 01:00:00.000 1.00000000  24.00000000
196-31020-005   2019-03-25 01:00:00.000 1.00000000  24.00000000
196-31020-005   2019-03-25 01:00:00.000 1.00000000  24.00000000
196-31020-005   2019-04-01 01:00:00.000 1.00000000  24.00000000
196-31020-005   2019-04-08 01:00:00.000 1.00000000  24.00000000
196-31020-005   2019-04-29 01:00:00.000 1.00000000  24.00000000
196-31020-005   2019-05-06 01:00:00.000 1.00000000  24.00000000
196-31020-005   2019-05-13 01:00:00.000 1.00000000  24.00000000
196-31020-005   2019-05-27 01:00:00.000 1.00000000  24.00000000
196-31020-005   2019-06-03 01:00:00.000 1.00000000  24.00000000
196-31020-005   2019-06-10 01:00:00.000 1.00000000  24.00000000
196-31020-005   2019-07-01 01:00:00.000 1.00000000  24.00000000
196-31020-005   2019-07-08 01:00:00.000 1.00000000  24.00000000
196-31020-005   2019-07-15 01:00:00.000 1.00000000  24.00000000
196-31020-005   2019-07-29 01:00:00.000 1.00000000  24.00000000
196-31020-005   2019-08-05 01:00:00.000 1.00000000  24.00000000
196-31020-005   2019-08-12 01:00:00.000 1.00000000  24.00000000
196-31020-005   2019-08-26 01:00:00.000 1.00000000  24.00000000
196-31020-005   2019-09-02 01:00:00.000 1.00000000  24.00000000

The query in the question looks fine. I don't see any syntax errors. Are you showing the actual query that you are trying to run, or some cut-down version? — Vladimir Baranov, Sep 18 '17 at 12:34
I agree with Vladimir, executes fine. Since it states Line 44, I suspect this is part of a larger query and the problem lies elsewhere — SE1986, Sep 18 '17 at 13:28
@SEarle1986 that is the code as it is. The only thing I have above this is variations of the same query which I was playing around with before I came to this.....I have commented out all these such that the only lines the will run are the one I posted on here — user2355773, Sep 18 '17 at 14:07
@user2355773 are you sure the error is related to this code? It's reporting Line 44 and your code has less rows. The second strange thing it reports Incorrect syntax near **'order'**. Note, it's lowercase, but your Order in **over (Order by item )** is Upperecase. I tryed to provoke similar error and the word Order was reported uppercase if I write it uppercase, so maybe it's NOT a row you are thinking about? — sepupic, Sep 19 '17 at 09:04
@user2355773 And please, update your question with the result of SELECT @@version — sepupic, Sep 19 '17 at 09:05
@sepupic I have updated the question and added the results I get. as you can see the rolling_qty field is not the running total but the overall total. — user2355773, Sep 19 '17 at 15:31
@user2355773 Sorry, but I still don't see the result of SELECT @@version — sepupic, Sep 19 '17 at 15:37

Gordon Linoff · Answer 1 · 2017-09-18T23:40:32.487

0

This is too long for a comment.

Even though you are using SQL Server 2012, the actual functionality depends on the compatibility level. You can query for the compatibility level using:

SELECT name, compatibility_level FROM sys.databases

This should be at least 110 for you to use the order by with sum(). To change the compatibility, refer to the documentation. If you have an older compatibility (perhaps less than 100 is needed), then you can get this error.

edited Sep 18 '17 at 23:40

answered Sep 18 '17 at 11:30

Gordon Linoff

1,242,037
58
646
786

1

Actually, this is not true. At least, for SQL Server 2014 Express. `SUM(...) OVER (ORDER BY ...)` worked with compatibility level 100 of the user database in my test. – Vladimir Baranov Sep 18 '17 at 12:09

sql running totals

1 Answers1