query using Case when

0

Can anyone help me with this query, please? I'm having a hard time organizing this 'Case when' I think

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 want to go, it's the first time I try a more different query, so to speak.

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 get all the accounts in a distinct and on the same line have the list

Conta | TotalCreditosEntrada | TotalCreditosSaida
    
asked by anonymous 14.05.2018 / 17:13

1 answer

2

You do not need to do query different, the basics already solve:

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;

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

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

    
14.05.2018 / 17:26