Bring the sum of IDS and data into two tables using SQL

Question

I need to make an SQL to bring me the data of:

`DATA | QTDE IDTa | QTDE IDTb | VALORa | VALORb | Saldo (diferença de VALORa - VALORb) `

However I am not able to bring the data correctly with this SQL follows example: https://www.db-fiddle.com/f/nU2Ds1qon4jXJd1jk24spQ/1

I need you to leave like this:

    DATA   | TITULOS A PAGAR | TITULOS A RECEBER | VALOR A RECEBER | VALOR A PAGAR | SALDO
01/10/2018 |         5       |         5         |    R$ 30.000    |  28.000       |  R$ 2.000

In this case he needs to add all the Ids of the downloaded table while the due date equals the due date, I want to know how many securities are due each day as well as the ones I have to pay each day and show the balance the difference between the two.

Answer 1

There are some things I think you need to take into consideration, first it’s your JOIN, it doesn’t have an ON and looking at the structure of the two tables I don’t see a link between them for a JOIN.

Another thing is the lack of a GROUP BY to be clear which column (I imagine by your output is the date) you are grouping items to add up.

I would make the union of two distinct selects (one of revenue and one of expense, being that of expense with negative values), group the result by date and sum all values (as expense is negative in the sum it would subtract

SELECT U.datavencto, SUM(U.valorprogramado)
FROM (
    SELECT datavencto, valorprogramado
    FROM baixareceita

    UNION ALL

    SELECT datavencto, valorprogramado * (-1)
    FROM baixagasto
) U
GROUP BY U.datavencto

[EDIT]

I put the other fields you wanted in return

SELECT U.datavencto, SUM(U.qtd_receber), SUM(U.qtd_pagar), SUM(U.valor_receber), SUM(U.valor_pagar), SUM(U.valor_total)
FROM (
    SELECT datavencto, COUNT(idbaixareceita) AS qtd_receber, 0 AS qtd_pagar, SUM(valorprogramado) AS valor_receber, 0 AS valor_pagar, SUM(valorprogramado) AS valor_total
    FROM baixareceita
    GROUP BY datavencto

    UNION ALL

    SELECT datavencto, 0 AS qtd_receber, COUNT(idbaixagasto) AS qtd_pagar, 0 AS valor_receber, SUM(valorprogramado) AS valor_pagar, SUM(valorprogramado)*(-1) AS valor_total
    FROM baixagasto
    GROUP BY datavencto
) U
GROUP BY U.datavencto