Subtract all values from data

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开发者_JAVA技巧.

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

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.

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