Kevin Hendricks - 10 days ago 5

SQL Question

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.

Answer

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
```

Source (Stackoverflow)

Comments