Fastest way to calculate summary of database field

Question

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?]

Answer 1

Here's a way of doing it in SQL. You'll have to test if it's faster to do it anotherway!

SELECT Name, SUM(CASE WHEN Sign = '+' THEN Value ELSE -Value END)
FROM table
GROUP BY Name

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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