Efficient Adding Value Of Every Row To The Next

Hello Everyone! In this article, we want to discuss a nice function of adding the value of every row to the next in SQL Server. Before we get started, if you want to know about using multiple columns with “Not In”, please go through the following article: SQL: Can you use multiple columns for NOT IN .

Sometimes we need to select data with the auto-generated sequence “Serial No” from the table. It may have an auto-generated column in your table. But when you select data with some condition, the column is may not be sequential. Then what you do?? It’s very simple to generate an auto-sequential number with “ROW_NUMBER()“.

Please see the table structure first:


Query to show adding value of every row to the next

With cte_Test(Sequence,PartNO,Whole)
As
(
      SELECT ROW_NUMBER() OVER(ORDER BY Dealer) AS 'Sequence' ,PartNO,Whole FROM ATable
)

SELECT t1.PartNO,SUM(T2.Whole) AS Value FROM cte_Test t1
CROSS JOIN cte_Test t2 where t2.Sequence <= t1.Sequence
GROUP BY t1.PartNO
ORDER BY 2

You can use the query also,

SELECT t1.PartNO,SUM(T2.Whole) AS Value FROM ATable t1
CROSS JOIN ATable t2 where t2.SerialNo <= t1.SerialNo
GROUP BY t1.PartNO
ORDER BY 2