Calculating a running count & running total across customers with SQL

running-count, sql, sql-server, sql-server-2012

Solution

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]

Problem

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?

Original source

Related problems