MySql query to find difference between same column with condition

Question

I have this table register:

id quantity type
 1 |     10 | in
 2 |       5 | in
 1 |       3 | out
 1 |       2 | out
 2 |       5 | out
 3 |       2 | in
 3 |       1 | out 

I want the balance of each stock *sum of type='in' - sum of type= 'out'*.

Desired output would be:

1 | 5
2 | 0
3 | 1

I also have another table item:

id | name
1  | A
2  | B
3  | C

Is it possible to view the output with the item name instead of the id? So the final result is like:

A | 5
B | 0
C | 1

Answer 1

The basic idea is conditional aggregation --case inside of sum(). You also need a join to get the name:

select i.name,
       sum(case when r.type = 'in' then quantity
                when r.type = 'out' then - quantity
                else 0
            end) as balance
from register r join
     item i
     on r.id = i.id
group by i.name;

Answer 2

Acccording to description as mentioned in above question,as a solution to it please try executing following SQL query

SELECT i.name,
    @in_total:= (select sum(quantity) from register where type = 'in'
        and id = r.id group by id),
    @out_total:= (select sum(quantity) from register where type = 'out'
        and id = r.id group by id),
    @balance:= (@in_total - @out_total) as balance
FROM `register`
as r join item i on r.id = i.id group by r.id 
CROSS JOIN (SELECT @in_total := 0, 
                   @out_total := 0, 
                   @balance := 0) AS user_init_vars