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

Question

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

Navigation

#1 by (1 votes)

2

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

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

But I am not able to bring the data correctly with this SQL example: link

I need to get out 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 in the table while the maturity date equals the maturity date, ie I want to know how many securities matures each day as well as the ones I have to pay each day and display the balance the difference of the two.

mysql sql

asked by anonymous 17.10.2018 / 20:41

1 answer

Flexbox leaving empty spacing How to upload image using Spring Boot?

score 1 · Accepted Answer

There are some things that I think you need to take into account, first is your JOIN, it does not have an ON and looking at the structure of the two tables I do not see a link between them for a JOIN.

Another thing is the lack of a GROUP BY to be clear by which column (I imagine for your exit that 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 expenditure, being that of spending with negative values), would group the result by date and sum all values (how 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 that you wanted as a 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