Subtract all values from data

Question

Table data look like this

id val
1 4
2 2
3 1

I want result of subtract valu of val field in one sql statement. like it should be like 4-2-1 = 1 if order by id asc, 1-2-4 = -5 if order by id desc.

Answer 1

You can try this

DECLARE @Table TABLE(
     ID INT,
     Val INT
)

INSERT INTO @Table (ID,Val) SELECT 1, 4
INSERT INTO @Table (ID,Val) SELECT 2, 2
INSERT INTO @Table (ID,Val) SELECT 3, 1

SELECT  SUM(Val * CASE WHEN RowID = 1 THEN 1 ELSE -1 END)
FROM    (
      SELECT *,
        ROW_NUMBER() OVER (ORDER BY ID) RowID
      FROM @Table
     ) sub

Answer 2

You can declare a variable and increment it in the select statement:

declare @sum float

select @sum = case when @sum is null then value else @sum - value end
from YourTable
order by id

select @sum

To reverse the subtraction order, change order by id to order by id desc.

Answer 3

if you want to use just the sql without temp tables or variables:

select fromid.val - sumid.val

from (

select val

from t

where id = (

    select min(id)

    from t

  )

) fromid cross join (

select sum(val) as val

from t

where id > (

      select min(id)

      from t

    )

) sumid