CODEWITHSUNDEEP
sqldatabaseoracle-databasesubquery
Kevin Hendricks
Kevin Hendricks

Reputation: 13

SQL Query: Looking to calculate difference between two columns both in different tables?

I'm looking to calculate the difference between the sum of two different columns in two different tables. Here's what I have:

SELECT sum(amount)
FROM variable_in
where user_id='111111'
minus
SELECT sum(amount)
FROM variable_out
where user_id='111111'

When I do this, I just get an output of the first query results. How do I have it execute both queries (for the in and out tables) as well as have it minus the variable_out total for the amount column? Since they are both going to be positive integers.

Thanks in advance! Most of the other tips I've seen have been overly complex compared to my issue.

Upvotes: 1

Views: 14226

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270091

How about moving the queries to the from clause and using -:

SELECT in_amount - out_amount
FROM (SELECT sum(amount) as in_amount
      FROM variable_in
      WHERE user_id = '111111'
     ) i CROSS JOIN
     (SELECT sum(amount) as out_amount
      FROM variable_out
      WHERE user_id = '111111'
     ) o;

Your query is confusing the set operation "minus" with the numerical operator -. Admittedly, they do have the same name. But minus works with sets, not numbers.

I should point out that you can put the nested queries in the FROM clause and use the results like numbers ("scalar subqueries"):

SELECT ((SELECT sum(amount) as in_amount
         FROM variable_in
         WHERE user_id = '111111'
        ) -
        (SELECT sum(amount) as out_amount
         FROM variable_out
         WHERE user_id = '111111'
        ) o
       ) as diff
FROM dual;

Upvotes: 0

Opochitsky Dimitry
Opochitsky Dimitry

Reputation: 64

it's very simple...

select

   (select sum(amount) from variable_in where user_id='111111')
      -
   (select sum(amount) from variable_out where user_id='111111')

as amount;

Upvotes: 1

Related Questions