CODEWITHSUNDEEP
sqlpostgresql
Boobie
Boobie

Reputation: 103

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 takes the difference between these two tables, to compute the following table (say table Z):

A
-
1
-2
1
2
-3

Upvotes: 8

Views: 33633

Answers (2)

Daniel Williams
Daniel Williams

Reputation: 9304

Marcelo is 100% right - in a true relational database the order of a result set is never guaranteed. that said, there are some databases that do always return sets in an order.

So if you are willing to risk it, here is one solution. Make two tables with autoincrement keys like this:

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

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

Then fill them with the 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 your 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

I would cross my fingers first though! If there is any way you can get a proper key in your table, please do so.

Cheers,

Daniel

Upvotes: 2

p.campbell
p.campbell

Reputation: 100567

It's not clear what you want. Could it be this?

SELECT  (SELECT SUM(A) FROM X) - 
        (SELECT SUM(A) FROM Y)
        AS MyValue

Upvotes: 14

Related Questions