How to group subtraction of equal fields in MySQL?

Question

How to group subtraction of equal fields in MySQL?

Navigation

#1 by (4 votes)

3

I need to make an appointment that returns the employee's current balance. Being that in my modeling I separate the debts and credits of the employee by the fields type 1 for credits and 2 for debits. My query is not bringing the total value grouped by 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

mysql query

asked by anonymous 10.12.2015 / 21:56

1 answer

How to return a part of a string (an html element) in php? How to perform UPDATE with PDO in PHP?

score 4 · Accepted Answer

The function IF can be useful since you resolve with just one query :

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

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

If you want to get the 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 "for each line, if the type is 1, add the valor in the credits. Otherwise, add 0 " . The same rationale applies to debits.

The syntax of the IF function is

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