How to group subtraction of equal fields in Mysql?

Question

I need to make an appointment to return the current balance of the employee. Being that in my modeling I separate the debts and credits of the employee by the type fields 1 for credits and 2 for debts. My query is not bringing the total amount grouped per employee, it returns only the subtraction of the first record found.

SELECT 
 (SELECT SUM(valor) from `conta_corrente_funcionario` c WHERE `tipo` = '1') -
 (SELECT SUM(valor) from `conta_corrente_funcionario` d WHERE `tipo` = '2') as total

FROM conta_corrente_funcionario` totais
    group by
    totais.idFuncionario

Answer 1

The function IF can be useful because you solve with only one query:

SELECT
   SUM( IF( tipo='1', valor, -valor ) ) AS total
FROM
   conta_corrente_funcionario
GROUP BY
   idFuncionario

In this way, according to the tipo the value will be added with reversed sign, that is, the same as subtracted (it may be necessary to change the "-" side in the real case, depends on whether you are using popular terminology or accounting for what is debit and what is credit).

If you want to get values in separate columns:

SELECT
   SUM( IF( tipo='1', valor, 0 ) ) AS creditos,
   SUM( IF( tipo='2', valor, 0 ) ) AS debitos
FROM
   conta_corrente_funcionario
GROUP BY
   idFuncionario

In this case we are saying that "for each line, if the type is 1, add the valor credits. Otherwise, add 0". The same reasoning applies to debts.

The syntax of the function IF is

IF( condição de teste, valor retornado se verdadeiro, valor retornado se falso )