Select rows per N and do a SUM

Question

I have a table in my SQL Server database with the following contents in it:

Date        | Amount
------------|----------
2012-12-17  | 9.00
2012-12-18  | 8.00
2012-12-19  | 0.00
2012-12-20  | 1.50
2012-12-21  | 2.50
2012-12-22  | 0.00
2012-12-23  | 0.00
2012-12-24  | 0.00
2012-12-25  | 0.00
2012-12-26  | 4.00
2012-12-27  | 2.00
2012-12-28  | 7.00

What I want to do is take 3 rows per select and SUM the Amount. If the total of the SUM is 0, then it should delete those 3 records. Otherwise it should just leave them alone and take the next 3 records and do the same checks.

So in this case only the following three records should get deleted from the table, since they are the only ones where the SUM would result in 0.

2012-12-23  | 0.00
2012-12-24  | 0.00
2012-12-25  | 0.00

How can I do his in SQL Server?

Answer 1

You can use ROW_NUMBER to make 3 element groups and calucalte sum.

WITH cte AS
(
  SELECT *, rn = (ROW_NUMBER() OVER(ORDER BY [Date]) - 1) / 3
  FROM #tab
), cte2 AS
(
  SELECT *, [sum] = SUM(Amount) OVER (PARTITION BY rn ORDER BY [Date])
  FROM cte
)
SELECT *
FROM cte2
WHERE [sum] = 0;

LiveDemo

And with DELETE:

WITH cte AS
(
   SELECT *, rn = (ROW_NUMBER() OVER(ORDER BY [Date]) - 1) / 3
   FROM #tab
), cte2 AS
(
   SELECT *, [sum] = SUM(Amount) OVER (PARTITION BY rn ORDER BY [Date])
   FROM cte
)
DELETE t
FROM #tab t
JOIN cte2 c
  ON t.[Date] = c.[Date]
WHERE [sum] = 0;

SELECT *
FROM #tab;

LiveDemo2

EDIT:

If your data can contain negative values you can use:

WITH cte AS
(
  SELECT *, rn = (ROW_NUMBER() OVER(ORDER BY [Date]) - 1) / 3
  FROM #tab
), cte2 AS
(
  SELECT rn, [sum] = SUM(Amount)
  FROM cte
  GROUP BY rn
)
SELECT c.*
FROM cte c
JOIN cte2 c2
  ON c.rn = c2.rn
WHERE [sum] = 0;

LiveDemo3