Tuesday, June 30, 2009

How to calculate column value as summation of all previouse values

This is the demonstration of how to calculate a column value that depends upon sum of all previous values in that column. For example check following two tables
Actual Data:
ProductIdNameQuantity
1Product A10
2Product B10
3Product C10
Required Output:
ProductIdNameCal Quantity
1Product A10
2Product B20
3Product C30

SQL Server Query:

SELECT P.ProductId, P.Name, SUM(PC.Quantity) AS Cal Quantity
FROM Products AS P LEFT OUTER JOIN Products AS PC
ON PC.ProductId <= P.ProductId
GROUP BY P.ProductId, P.Name

No comments: