1

I am trying to use the Rows between unbounded preceeding... method as per how to get cumulative sum

I am running MS Server 2012.

The query is using a table created in a CTE [tbl2]:

SELECT   [RowID]
       , [GroupID]
       , [NumericalData]
       , Sum([NumericalData]) 
         Over (Partition By [GroupID] 
         order by [RowID] 
         ROWS between  UNBOUNDED PRECEDING and CURRENT ROW) as Cumulative

   FROM TBL2
   GROUP BY [ROWid]
    , [groupID]
    , [NumericalData]
  Order By ROW

I get the following error message: Msg 102, Level 15, State 1, Line 103 Incorrect syntax near 'ROWS'.

Any one know what I am doing wrong?

Thanks in advance

Community
  • 1
  • 1
RobTovey
  • 21
  • 4
  • Looks like it should work for you except for the final group by/order by. Is the group by really necessary? Do you have a column named `ROW`? – Mikael Eriksson Apr 30 '14 at 12:38
  • 1
    It does not look like you are using SQL Server 2012. What does `select @@version` tell you? – Mikael Eriksson Apr 30 '14 at 12:38
  • I get Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) – RobTovey Apr 30 '14 at 13:03
  • I am using Management Studio 2012 on my laptop but the server runs off 2008. So its not going to work. Thanks for the advice. – RobTovey Apr 30 '14 at 13:05

1 Answers1

1

That clause is not needed for the cumulative sum in SQL Server. In addition:

  • You are mixing group by with the window function. Although allowed, this may not be what you really want.
  • Your order by is invalid.

Perhaps this is what you want:

SELECT   [RowID], [GroupID], [NumericalData],
         Sum([NumericalData]) Over (Partition By [GroupID] order by [RowID]) as Cumulative
FROM TBL2
ORDER BY [RowID];

EDIT:

I want to note that the original query (with the order by clause fixed) does work in SQL Fiddle. Perhaps the problem is the version of SQL Server.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    If you omit the `rows` clause you are using the default and that is `range`. If `RowID` is not unique per `GroupID` you will end up with a different result. Unless you really want the result produced by `range` you should use `rows` for performance reasons. – Mikael Eriksson Apr 30 '14 at 12:46
  • @MikaelEriksson . . . I suspect that the query is not really what the OP wants. Aggregating by the numeric value and then including it in a cumulative sum would not be a common operation. – Gordon Linoff Apr 30 '14 at 12:53
  • Agree that the final group by clause is not what he wants and probably not the order by either, at least not like that. Just wanted to point out that "That clause is not needed for the cumulative sum in SQL Server." is not entirely correct. It can have impact on the result and it will have impact on performance. – Mikael Eriksson Apr 30 '14 at 13:03
  • I have a list of process times in a manufacturing flow and I am trying to create a cumulative value so that I can perform a further calculation. I have now got an inner join working so have the results I need. Thanks for all of your help. – RobTovey Apr 30 '14 at 13:09