Subtract sums of fields in two separate tables

Asked

Viewed 2,439 times

7

I need to add the value of a column in SQL, so it looks like this:

SELECT SUM(qtdsaco) FROM armazem

This code works. Now I need to add the value of the other table, here it is:

SELECT SUM(qtdsaco) FROM armazem2

So I needed to take the result of both and subtract. How do I?

mysql sql

  • You want to sum it all up and subtract from each other?

  • Yes I want to sum everything from one column and everything from another and subtract, the value of the qtdsaco column from the store - the qtdsaco value stores 2 two and take the result

5 answers

4

The SELECT serves to select information anyway. It doesn’t even need to be from the database. It can select data if SQL sub-expressions, including other SELECTs. Then you can make each of them as a sub-expression and replace the two in the SELECT leading.

SELECT (SELECT SUM(qtdsaco) FROM armazem) - (SELECT SUM(qtdsaco) FROM armazem2)

Behold working in the Sqlfiddle. Also put on the Github for future reference.

Obviously you can get the same result with SQL expression variations. I found this one more appropriate because it is simple and clearly demonstrates the intention. If I had other requirements I would choose another way. I followed what the question asked ipsis Litteris.

  • made that mistake #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')
LIMIT 0, 25' at line 1

  • There are things there in the error that are not in your question. If you have put a LIMI should be in the description of the problem. Now you have answers given on top of what you asked and the problem was different from what was described. It was a problem. I don’t guarantee, but if you edit the question by actually putting what you want, maybe you can save the question.

  • Master worked well here I wanted to score you answered very fast and I can not because I have not yet 15 points but when I have I will score you here thanks for responding

4


You will have to see which table comes first and do so by playing the value in a total:

SELECT (SUM(qtdsaco) - (SELECT SUM(qtdsaco) FROM armazem2)) total FROM armazem

  • I have to create a field in one of the two tables with total name to launch the value there in the value of the result you speak ?

  • no, if you put it this way, your result will come with a column already named as total.

  • Thank you for answering worked thanks

  • @checkmate if you need to, give a shout here. Remembering that there are several ways to solve your problem. This one here would be only 1.

  • Brigado patraoo

3

Basing myself in this other answer of mine, I would do so:

SELECT a.c - b.d AS qtdsaco
FROM (SELECT SUM(qtdsaco) AS c FROM armazem) a,
     (SELECT SUM(qtdsaco) AS d FROM armazem2) b

  • Thank you for answering friend

3

Make use of subselects:

select (SELECT SUM(qtdsaco) FROM armazem) - (SELECT SUM(qtdsaco) FROM armazem2)

1

You can do a JOIN as follows.

declare @armazem table(qtdsaco int)
declare @armazem2 table( qtdsaco int)
insert into @armazem values(112),(3),(432),(22),(1),(5),(4),(3),(2)
insert into @armazem2 values(1),(2),(3),(4),(6),(5),(4),(3),(2)

select SUM(a1.qtdsaco) - SUM(a2.qtdsaco) total
from @armazem a1
join  @armazem2 a2 on 1 = 1

Note; as the database was not specified, this was done in sql server.

  • Thank you for answering friend

Browser other questions tagged mysql sql

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