Running count for each 2 rows

Question

I am trying to calculate running count for each 2 rows like below,

CREATE TABLE sales
(
     EmpId INT, 
     Yr INT, 
     Sales DECIMAL(8,2)
)

INSERT INTO sales (EmpId, Yr, Sales)
VALUES (1, 2005, 12000), (1, 2006, 18000), (1, 2007, 25000),
       (1, 2008, 25000), (1, 2009, 25000),
       (2, 2005, 15000), (2, 2006, 6000), (2, 2007, 6000)

SELECT 
    EmpId, Yr, sales, 
    SUM(Sales) OVER (PARTITION BY empid ORDER BY empid ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS TotalSales2
FROM 
    sales

Output:

EmpId   Yr      sales   TotalSales2
-----------------------------------
  1     2005    12000      12000
  1     2006    18000      30000
  1     2007    25000      55000
  1     2008    25000      68000
  1     2009    25000      75000
  2     2005    15000      15000
  2     2006     6000      21000
  2     2007     6000      27000

But expected output:

EmpId   Yr     Sales    TotalSales2
-----------------------------------
  1     2005    12000   12000
  1     2006    18000   30000
  1     2007    25000   25000   
  1     2008    25000   50000
  1     2009    25000   25000   
  2     2005    15000   15000
  2     2006     6000   21000
  2     2007     6000    6000

What am I doing wrong in this query?

Note: SQL Servre version is 2012.

Answer 1

SELECT EmpId, Yr, Sales, 
    CASE WHEN ROW_NUMBER() OVER (PARTITION BY EmpId ORDER BY yr) % 2 = 0 
    THEN sales + lag(sales, 1, 0) OVER (PARTITION BY empid ORDER BY yr) 
    ELSE sales 
    END AS TotalSales2
FROM sales

Lag returns the previous row's value - when row_number() is even, add the current row's value to the previous row - otherwise, just show the sales for the current row. Partition each by EmpId, order each by yr - output matches the expected.

Also, thanks so much for adding the DDL/sample data.

Answer 2

The expression:

SUM(Sales) OVER (PARTITION BY empid 
                 ORDER BY empid 
                 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

calculates the sum considering the current row and the 2 rows immediately preceding it. So it actually calculates a rolling sum, which is what you really don't want.

I think you are actually looking for something like the following:

;WITH CTE_Group AS (
    SELECT EmpId, Yr, sales,        
          (ROW_NUMBER() OVER (PARTITION BY empid ORDER BY yr) + 1 ) / 2 AS grp
    FROM sales      
)
SELECT EmpId, Yr, sales,
       SUM(sales) OVER (PARTITION BY empid, grp 
                        ORDER BY yr) AS TotalSales2
FROM CTE_Group

The above query uses a CTE in order to calculate field grp: the value of this field is 1 for the first two records of an empid partition, 2 for the next two records, and so on.

Using grp we can calculate the running total of sales for groups of 2 as is the requirement of the OP.

Demo here

Edit:

To offset a larger group of records try using (credit goes to @Max Szczurek for pointing this out):

(ROW_NUMBER() OVER (PARTITION BY empid ORDER BY yr) - 1 ) / n AS grp

where n is the number of records each group contains.