CODEWITHSUNDEEP
mysqlsql
awang
awang

Reputation: 13

sql - subtract values from different tables

im new to sql. i tried to make simple subtraction of two columns from two tables. for example:

Table1 = id, amount, value

Table2 = id, amount

how can i subtract amount 1 with amount 2 without adding total amount in each column. because i want to view it in row.. this is what i have tried so far but still fail..

"SELECT ( SELECT amount FROM table1 ) - ( SELECT amount FROM table2 ) AS difference"

btw i want to ask, is there any way that sql can auto generate table or extra column to view the difference / result of subtraction?

thank you

Upvotes: 1

Views: 13598

Answers (2)

Sas
Sas

Reputation: 278

To subtract the column values from two different tables first JOIN the tables and then use the SUBTRACT operator to get the difference

SELECT t1.id, (t1.amount-t2.amount) as "DIFFERENCE" FROM table1 t1
INNER JOIN table2 t2
ON t1.id = t2.id

for more info on JOINS SQL JOIN and different types of JOINs

Upvotes: 0

amulya349
amulya349

Reputation: 1238

You can directly perform operations on multiple columns and show that result in a new column which you can name using as clause. You can perform join to do operation between two columns of different tables in a similar fashion. To answer your question, following is the SQL query:

SELECT a.id, a.amount-b.amount as Difference FROM table1 a
INNER JOIN table2 b
ON a.id=b.id

Upvotes: 2

Related Questions