Filter select so that the result does not show all records

Question

Filter select so that the result does not show all records

Navigation

#1 by (3 votes)

0

I have a table with the following structure:

codigo / signo /importe

With this select I get the following:

codigo / cargo / abono / saldo

But I want it to not show those that have zero balance (0), how do I filter that?

SELECT codigo,
SUM(CASE WHEN signo = '+' THEN importe ELSE 0 END) cargo,
SUM(CASE WHEN signo = '-' THEN importe ELSE 0 END) abono,
SUM(CASE WHEN signo = '+' THEN import ELSE 0 END) - SUM(CASE WHEN signo = '-' THEN importe ELSE 0 END) saldo
FROM tabla
group by codigo

mysql

asked by Piropeator 02.08.2017 в 20:46

source

1 answer

Protect folder in apache [closed] How is this Javascript error interpreted?

score 3 · Accepted Answer

Use HAVING :

SELECT  codigo,
        SUM(CASE WHEN signo = '+' THEN importe ELSE 0 END) cargo,
        SUM(CASE WHEN signo = '-' THEN importe ELSE 0 END) abono,
        SUM(CASE WHEN signo = '+' THEN import ELSE 0 END) - 
        SUM(CASE WHEN signo = '-' THEN importe ELSE 0 END) saldo
FROM tabla
GROUP BY codigo
HAVING SUM(CASE WHEN signo = '+' THEN import ELSE 0 END) - 
       SUM(CASE WHEN signo = '-' THEN importe ELSE 0 END) <> 0
;

Or a derived table:

SELECT *
FROM (  SELECT  codigo,
                SUM(CASE WHEN signo = '+' THEN importe ELSE 0 END) cargo,
                SUM(CASE WHEN signo = '-' THEN importe ELSE 0 END) abono,
                SUM(CASE WHEN signo = '+' THEN import ELSE 0 END) - 
                SUM(CASE WHEN signo = '-' THEN importe ELSE 0 END) saldo
        FROM tabla
        GROUP BY codigo) as t
WHERE t.saldo <> 0;