Get row wise sum considering all the previous sums in SQL Server

Question

Tried to get row wise sum considering all the previous sums and got output shown below.

Can please anybody tell me what i'm missing?

SELECT
  ProductId,
  ProductName,
  ProductAmount As ProductActualAmount,
  CASE
    WHEN (CASE
        WHEN LEAD(ProductAmount) OVER (ORDER BY ProductId) IS NULL THEN 0
        ELSE LEAD(ProductAmount) OVER (ORDER BY ProductId)
      END) != 0.00 THEN (ProductAmount + CASE
        WHEN LAG(ProductAmount) OVER (ORDER BY ProductId) IS NULL THEN 0
        ELSE LAG(ProductAmount) OVER (ORDER BY ProductId)
      END)
    ELSE (CASE
        WHEN LEAD(ProductAmount) OVER (ORDER BY ProductId) IS NULL THEN 0
        ELSE LEAD(ProductAmount) OVER (ORDER BY ProductId)
      END)
  END AS SumAmount
FROM ProductSales

Output:

Expected Output:

ProductId   ProductName   ProductActualAmount   SumAmount
---------------------------------------------------------
1           Rexona        35.00                 35.00
2           Liril         40.00                 75.00
3           Dove          45.00                 120.00         
4           Pears         50.00                 170.00

Answer 1

Using SUM() OVER will give your expected result:

SELECT ProductId, 
       ProductName,
       ProductAmount AS ProductActualAmount,
       SUM(ProductAmount) OVER (ORDER BY ProductId) AS SumAmount
FROM ProductSales

Demo on db<>fiddle

Answer 2

You can also use a correlated subquery as

SELECT ProductId, 
       ProductName, 
       ProductAmount AS ProductActualAmount,
       (
         SELECT SUM(PS2.ProductAmount)
         FROM ProductSales PS2
         WHERE PS1.ProductAmount >= PS2.ProductAmount 
       ) SumAmount
FROM ProductSales PS1;

Demo