Error summing using Join and sub-query - MYSQL

Asked

Viewed 37 times

0

Good afternoon,

I have two tables that I need to perform the sum but it is grouping.

*Correction:

The rule of business is that there are notes , in which the user can make several deposits upon the id of the notes. The problem is that when making the sum of each deposit that was made in id_note , he ends up grouping.

Table 1 with NOTES is like this:

            id_nota  | 

            ----------
            1        |
            2        |
            3        |
            4        |

Table 2 of name DEPOSIT is like this:

        id_deposito  |   id_nota |  valor_deposito |
        ---------------------------------------------
        1            |     1     |  200.00
        2            |     1     |  250.00
        3            |     3     |  300.00
        4            |     3     |  350.00
        5            |     4     |  400.00

When doing Join with these two tables it returns me each row with the sum of all and not the sum of each by id.

select *
from deposito 
cross join (SELECT SUM(valor_deposito) as amount FROM deposito AS amount) b
inner join notas on notas.id_nota = deposito.id_nota

He returns to me like this :

        id_deposito  |   id_nota |  valor_deposito | amount
        ---------------------------------------------
        1            |     1     |  200.00         | 1500.00
        2            |     1     |  250.00         | 1500.00
        3            |     3     |  300.00         | 1500.00
        4            |     3     |  350.00         | 1500.00
        5            |     4     |  400.00         | 1500.00

He should return me like this:

        id_deposito  |   id_nota |  valor_deposito | amount
        ---------------------------------------------
        1            |     1     |  200.00         | 450.00
        2            |     1     |  250.00         | 450.00
        3            |     3     |  300.00         | 650.00
        4            |     3     |  350.00         | 650.00
        5            |     4     |  400.00         | 400.00
mysql query

  • Without an explanation of your business rule and what the data means it is very difficult to give an answer. Your Join seems wrong to me, when you use CROSS there is no link, outside it seems strange to use a SUM within that.

  • 1

    I believe that your subselect should contain the clause GROUP BY id_note and should be an INNER JOIN USING id_note, although I don’t understand the purpose of your query.

2 answers

0

Try it like this:

SELECT *,
(SELECT SUM(deposito2.valor_deposito) FROM deposito deposito2 WHERE deposito2.id_nota = deposito.id_nota ) AS amount
from deposito 
inner join notas on notas.id_nota = deposito.id_nota

0

Take a test like this:

SELECT SUM(valor_deposito) FROM deposito INNER JOIN notas IN deposito.id_nota = notas.id GROUP BY notas.id;

Browser other questions tagged mysql query

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