G43beli G43beli - 1 year ago 64
SQL Question

Combine SQL Rows and get Value

I've a dataview with for example the following records:

Num Type Name
17 2 Luke
22 1 Luke
22 2 Sandy
10 1 Sandy
19 2 Tom
19 1 Tom

How can I combine for example all rows for Sandy and get the total of the Num row? But it should get the Type as operator (1 = plus, 2 = minus). So for Sandy it would be 10-22 = Total -12

The resulttable should look like this:

Num Type Name
4 null Luke
-12 null Sandy
0 null Tom

Is that even possible in pure SQL?

Thx for your help

Answer Source

Yes, this is possible:

select name,
       sum(case when type = 1 then num
                when type = 2
                then - num
           end) as num
from t
group by name;

I'm leaving out type from the query, because it doesn't seem useful.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download