Subtraction of multiple selects

Asked

Viewed 5,013 times

5

I am learning about SQL and I have a certain problem to do certain calculations.

I would like to subtract the values of selects below. I have tried to do the SUM ( A - B - C), but without success.

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
sql

3 answers

5


In this case it would be possible to transform all these queries into Queries, within a SELECT, each as a column, and then transform everything, again into a subquery in the FROM clause, and use the column names to add/subtract the way 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

1

It is simpler than it seems, it is only necessary to use the operator '-' between the two subquerys. Follow the operator documentation link: 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'

1

I didn’t 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 how to do the calculations in the main select.

I hope it helps.

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.