query using Case when

Question

Can anyone help me with this query please? I’m having difficulties in organizing this 'Case when' I believe

CREDITO | CONTA | TIPO
100         8    ENTRADA
30          9    ENTRADA
30         10    ENTRADA
1           8    SAIDA
10         11    ENTRADA
1           8    SAIDA
1          11    SAIDA
10         12    ENTRADA
15         12    ENTRADA
15         13    ENTRADA

look where I’m going, it’s the first time I try a more different query, let’s say so.

select c, sum(pos), sum(negat) from(
select conta as c,
Case when (tipo = 'ENTRADA') then sum(credito) end pos,
Case when (tipo = 'SAIDA') then sum(credito) end negat
from credito
) A group by c;

i need to pick up all the accounts in a distinct and on the same line have the list

Conta | TotalCreditosEntrada | TotalCreditosSaida

Answer 1

You don’t have to make a query different, the basic already solves:

SELECT a.conta, SUM(IF(a.tipo = 'ENTRADA', a.credito, 0)) AS pos, SUM(IF(a.tipo = 'SAIDA', a.credito, 0)) AS negat
FROM credito AS a
GROUP BY a.conta;

The IF checks the type of operation, if it is ENTRADA in the column pos will be the value and in the column negat will be 0. If it is SAIDA the exact opposite will occur.

Already the SUM will add all lines and the GROUP BY will split by account number.