Fastest way to calculate summary of database field

I have a ms-sql table 开发者_如何学编程with the following structure:

Name nvarchar; Sign nvarchar; Value int

example contents:

Test1, 'plus', 5

Test1, 'minus', 3

Test2, 'minus', 1

I would like to have totals per "Name". (add when sign = plus, subtract when sign = minus)

result:

Test1, 2

Test2, -1

I want to show these results (and update them when a new record is added)... and I'm looking for the fastest solution! [sproc? fast-forward cursor? calculate in .net?]

The fastest solution is to redesign your database to store the value as a signed integer. Storing the sign separately has no benefit whatsoever.

select Name, 
    sum(case 
            when Sign = 'plus' then Value 
            when Sign = 'minus' then -Value 
            else 0 
        end)  
from MyTable
group by Name

you should look up sql SUM() function: http://www.w3schools.com/sql/sql_func_sum.asp

Another variation

select Name,  
    sum(value*case  
            when Sign = 'plus' then 1
            when Sign = 'minus' then -1  
            else 0  
        end)   
from MyTable 
group by Name