Subtraction of multiple selects

5

I'm learning about SQL and I'm having a problem making certain calculations.

I would like to subtract values from selects below. I've already tried to SUM ( A - B - C) , but to no avail.

My code:

(SELECT sum (dr.valortitulo)
from fin_docbase dbr
join fin_documento dr on (dr.coddocbase = dbr.cod)
where dbr.codpessoa = p.cod
and dr.codtpmovimento =  101
and dr.status = 'ABE') as totalreceber,

(SELECT sum (d.valortitulo)
from fin_docbase db
join fin_documento d on (d.coddocbase = db.cod)
where db.codpessoa = p.cod
and d.codtpmovimento =  100
and d.status = 'ABE') as totalpagar ,

(Select sum (dc.valortitulo)
from fin_docbase dbc
join fin_documento dc on (dc.coddocbase = dbc.cod)
where p.cod = dbc.codpessoa
and dc.codtpmovimento = 111
and dc.codtpcobranca in (18,16,14)
and dc.status in ('DEV','DVL')) as totalcheques
    
asked by anonymous 24.04.2014 / 14:53

3 answers

5

In case it would be possible to transform all these queries into subqueries, within a SELECT, each as a column, and then turn everything back into a subquery in the FROM clause, and use the column names to add / subtract form you want:

SELECT TOTAIS.totalreceber   -- fazendo operações com as colunas
       - TOTAIS.totalpagar   -- da subquery TOTAIS
       - TOTAIS.totalcheques 
FROM   (SELECT
               -- query A, que irá compor a coluna 'totalreceber'
               (SELECT Sum (dr.valortitulo) 
                FROM   fin_docbase dbr 
                       JOIN fin_documento dr 
                         ON ( dr.coddocbase = dbr.cod ) 
                WHERE  dbr.codpessoa = p.cod 
                       AND dr.codtpmovimento = 101 
                       AND dr.status = 'ABE')             AS totalreceber, 

               -- query B, que irá compor a coluna 'totalpagar'
               (SELECT Sum (d.valortitulo) 
                FROM   fin_docbase db 
                       JOIN fin_documento d 
                         ON ( d.coddocbase = db.cod ) 
                WHERE  db.codpessoa = p.cod 
                       AND d.codtpmovimento = 100 
                       AND d.status = 'ABE')              AS totalpagar, 

               -- query C, que irá compor a coluna 'totalcheques'
               (SELECT Sum (dc.valortitulo) 
                FROM   fin_docbase dbc 
                       JOIN fin_documento dc 
                         ON ( dc.coddocbase = dbc.cod ) 
                WHERE  p.cod = dbc.codpessoa 
                       AND dc.codtpmovimento = 111 
                       AND dc.codtpcobranca IN ( 18, 16, 14 ) 
                       AND dc.status IN ( 'DEV', 'DVL' )) AS totalcheques
       )
       AS TOTAIS -- nome da subquery que está na clausula FROM
                 -- contendo todas as colunas acima
    
24.04.2014 / 15:01
1

I did not get to test, but try with subselects.

SELECT A.totalreceber, B.totalpagar, C.totalcheques
FROM
((SELECT dr.coddocbase as totalreceber, sum (dr.valortitulo)
from fin_docbase dbr
join fin_documento dr on (dr.coddocbase = dbr.cod)
where dbr.codpessoa = p.cod
and dr.codtpmovimento =  101
and dr.status = 'ABE')) AS A,
((SELECT dr.coddocbase as totalpagar, sum (d.valortitulo)
from fin_docbase db
join fin_documento d on (d.coddocbase = db.cod)
where db.codpessoa = p.cod
and d.codtpmovimento =  100
and d.status = 'ABE')) AS B ,
((Select dr.coddocbase as totalcheques, sum (dc.valortitulo)
from fin_docbase dbc
join fin_documento dc on (dc.coddocbase = dbc.cod)
where p.cod = dbc.codpessoa
and dc.codtpmovimento = 111
and dc.codtpcobranca in (18,16,14)
and dc.status in ('DEV','DVL'))) as C
where A.coddocbase = B.coddocbase AND A.coddocbase = C.coddocbase

This way you will have to do the calculations in the main select.

I hope it helps.

    
24.04.2014 / 15:00
1

It's simpler than it sounds, you just need to use the '-' operator between the two subquerys. Follow the link for the operator documentation: Operator "-"

SELECT (
    (SELECT sum (dr.valortitulo)
    from fin_docbase dbr
    join fin_documento dr on (dr.coddocbase = dbr.cod)
    where dbr.codpessoa = p.cod
    and dr.codtpmovimento =  101
    and dr.status = 'ABE') 
    - 
    (SELECT sum (d.valortitulo)
        from fin_docbase db
        join fin_documento d on (d.coddocbase = db.cod)
        where db.codpessoa = p.cod
        and d.codtpmovimento =  100
        and d.status = 'ABE')
    ) as 'total a receber - total a pagar'
    
24.04.2014 / 15:00