Select inside math function

Question

This is most likely a beginner's question in SQL. Is it possible to use a select within a math expression?

For example, I have two tables: - table A with a column named id (primary key) and another column named val_A - table B with a column named id (primary key) and another column named val_B

I want to do something like:

select ((select val_A from A where id = 1) +
    (select val_B from B where id = 1)) as final_sum;

I'm using MySQL and it is throwing errors. I'm assuming that this is because the result of a select is a set and I want the numeric value of val_A and val_B to be make the sum.

Is there any way of doing this?

Thanks!

Answer 1

The query that you have:

select ((select val_A from A where id = 1) +
        (select val_B from B where id = 1)
       ) as final_sum

is correctly formed SQL in MySQL (assuming that the table and columns exist).

However, it assumes that each subquery only returns one row. If not, you can force it using limit or a function like min() or max():

select ((select val_A from A where id = 1 limit 1) +
        (select max(val_B) from B where id = 1)
       ) as final_sum

Or, possibly, you are trying to get the sum of all the rows with id = 1 in both tables:

select ((select sum(val_A) from A where id = 1) +
        (select sum(val_B) from B where id = 1)
       ) as final_sum

Answer 2

Yes you can do that, but a more proper query format would be:

SELECT (a.val_a + b.val_b) as final_sum
FROM a INNER JOIN b ON a.id = b.id
WHERE a.id = 1