Search code examples
mysqlsqljoinaggregationconditional-aggregation

MySql query to find difference between same column with condition

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
Solution

  • 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;