Geek Answers Handbook

Subtract values ​​from two different tables

Consider table X:

A
-
1
2
3
3
6

Consider table Y:

A
-
0
4
2
1
9

How do you write a query that accepts the difference between these two tables in order to calculate the following table (for example, table Z):

A
-
1
-2
1
2
-3
+5
source share
2 answers

It is not clear what you want. Maybe so?

SELECT  (SELECT SUM(A) FROM X) - 
        (SELECT SUM(A) FROM Y)
        AS MyValue
+8
source

Marcelo is 100% right - in this relational database the order of the result set is never guaranteed. that there are some databases that always return sets in order.

So, if you are willing to take risks, here is one solution. Create two tables with auto-increment keys:

CREATE TABLE Sets (
  id integer identity(1,1)
, val decimal
) 

CREATE TABLE SetY (
  id integer identity(1,1)
, val decimal
)

Then fill them with X and Y values:

INSERT INTO Sets (val) (SELECT * FROM X)
INSERT INTO SetY (val) (SELECT * FROM Y)

Then you can do this to get the answer:

SELECT X.ID, X.Val, Y.Val, X.val-Y.val as Difference
FROM Sets X
LEFT OUTER JOIN SetY Y
  ON Y.id = X.ID

! - , .

,

Daniel

+1

More articles:


All Articles