Return a sum in separate rows in mysql

Question

I have a table of statement of deposit and withdrawal of some customers, follows the table:

id_cliente|operação|valor |
----------+--------+------+
51298     |   01   | 50,00|
----------+--------+------+
51298     |   01   | 48,50|
----------+--------+------+
51298     |   02   | 13,67|
----------+--------+------+
51298     |   02   | 18,17|
----------+--------+------+

Transactions with amounts referring to 1 are deposits, with value 2 are withdrawals. How do I perform a query by returning the client balance value ? An sql that already returns the difference in values.

Answer 1

The IF function:

Mysql already has its own tool for conditional situations. It is the function IF:

IF( expressão de teste, valor se verdadeiro, valor se falso )

Applying to the case of the question:

Just one single SUM with IF to obtain the calculated balance in a single pass by DB:

SELECT SUM( IF( operacao='01', valor, -valor ) ) AS saldo FROM tabela

Upshot:

saldo
66.66

See working on SQL Fiddle.

Variants:

If to see a specific customer’s balance:

SELECT SUM(IF(operacao='01',valor,-valor )) AS saldo FROM tabela WHERE id_cliente=51298

If it goes to multiple clients in the same survey:

SELECT SUM( IF( operacao='01', valor, -valor ) ) AS saldo FROM tabela GROUP BY id_cliente

Answer 2

Replace column and table names:

select sum(case when operacao='01' then valor else 0 end) deposito,
       sum(case when operacao='02' then valor else 0 end) retirada,
       sum(case when operacao='01' then valor else -1*valor end) saldo
  from tabela
 where id_cliente = 51298

Answer 3

you can try this way

select sum(case when operacao=2 then valor*-1 else valor end) 
from tabela 
GROUP by id_cliente

Answer 4

Ready:

select 
   (sum(valor) from tabela where operacao = '01') - 
   (sum(valor) from tabela where operacao = '02') saldo

I am considering that the operating field is a varchar/string. If it is numeric remove the quotes.