Calculating a running count & running total across customers with SQL

Question

I have the following table (SQL Server 2012):

DID - cust id
GID - order id
AMT - order amt
Gf_Date - order date
SC - order reversal amount

I'm trying to calculate a running count of orders and a running total of sales by customer so that I can assign a flag to the point in time where a customer achieved cumulative sales of $1,000. As a first step, I've run this query:

Select
  [DID]
, [AMT]
, [Gf_Date]
, COUNT([GID]) OVER (PARTITION BY [DID] ORDER BY [Gf_Date]) [RunningGift_Count]
, SUM([AMT]) OVER (PARTITION BY [DID] ORDER BY [Gf_Date]) [CumlativeTotal]
FROM [dbo].[MCT]
WHERE [SC] is null
ORDER BY [DID]

But I get the error message:

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

I posted this earlier with the wrong error message pasted in. Regrets and apologies. What you see above is the result I'm getting. Someone commented that this syntax is incorrect. Now that all is in order, can someone tell me what I'm doing wrong?

[`ORDER BY`](http://msdn.microsoft.com/en-us/library/ms188385.aspx) must be the last statement. — Tim Schmelter, Jul 26 '13 at 23:28
Is it, then, that I can't do the RunningCount and RunningTotal in the same query? — Chris Howley, Jul 26 '13 at 23:33
It's possible in various ways. In SQL 2012 there are window functions using `UNBOUNDED PRECEDING` and there is always recursive CTE as good and fast option. I'll see if I can show you few examples. Until then check this links - http://www.sqlperformance.com/2012/07/t-sql-queries/running-totals and http://stackoverflow.com/questions/860966/calculate-a-running-total-in-sqlserver and try googling for 'SQL Server Running Totals' — Nenad Zivkovic, Jul 26 '13 at 23:42

Bogdan Sahlean · Answer 1 · 2013-07-27T06:02:35.760

You should use ROW_NUMBER (link) instead of COUNT:

DECLARE @Threshold NUMERIC(19,2)=1000; -- Use the same data type as `[AMT]`'s data type

Select
  [DID]
, [AMT]
, [Gf_Date]
--, COUNT([GID]) OVER (PARTITION BY [DID] ORDER BY [Gf_Date]) [RunningGift_Count]
, ROW_NUMBER() OVER (PARTITION BY [DID] ORDER BY [Gf_Date]) [RunningGift_Count]
, SUM([AMT]) OVER (PARTITION BY [DID] ORDER BY [Gf_Date]) [CumlativeTotal]
, CASE
      WHEN SUM([AMT]) OVER (PARTITION BY [DID] ORDER BY [Gf_Date]) >= @Threshold THEN 1
      ELSE 0
  END IsThresholdPassed
FROM [dbo].[MCT]
WHERE [SC] is null
ORDER BY [DID]

Calculating a running count & running total across customers with SQL

1 Answers1

Linked