1

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

jarlh
  • 42,561
  • 8
  • 45
  • 63
awang
  • 13
  • 1
  • 1
  • 6

2 Answers2

2

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
amulya349
  • 1,210
  • 2
  • 17
  • 26
  • Table aliases like A, B, C etc don't make much sense. Chose aliases that make you understand which table it is. I'd go with t1 and t2 for table1 and table2. (Silly table names, I know.) – jarlh Apr 13 '18 at 07:08
  • @jarlh LoL.. Ofcourse. It was just an example. We should name them which makes sense. – amulya349 Apr 13 '18 at 07:15
  • btw.. i have extra question.. Can we set the " as Difference" turn into one of the new column or new table in database?? – awang Apr 13 '18 at 07:44
  • @awang to set that as a new column or new table, you have to create a new table or alter some table. In my opinion, you can create a view for that query which will return you the same result as querying a table. To create the view, prepend this before the select statement : "CREATE VIEW differrence_view_name as ". Hope this helps – amulya349 Apr 13 '18 at 08:18
  • once you create the view, you can just query the view like : select * from difference_view_name it will give you the result – amulya349 Apr 13 '18 at 08:18
0

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

Sas
  • 278
  • 1
  • 12
  • 1
    Table aliases like A, B, C etc don't make much sense. Chose aliases that make you understand which table it is. I'd go with t1 and t2 for table1 and table2. (Silly table names, I know.) – jarlh Apr 13 '18 at 07:04
  • btw.. i have extra question.. Can we set the " as Difference" turn into one of the new column or new table in database?? – awang Apr 13 '18 at 07:44