Multi tool use

sql - getting sum of same column from multiple tables

I have a few tables in my DB. Let's call them table1, table2, table3.
All of them have a column named value.
I need to create a query that will return a single number, where this number is the sum of all the value columns from all the tables together...
I've tried the following way:

value

value

SELECT (SELECT SUM(value) FROM table1) + (SELECT SUM(value) FROM table2) + (SELECT SUM(value) FROM table3) as total_sum


But when at least one of the inner SUM is NULL, the entire total value (total_sum here) is NULL, so that's not very trustworthy.
When there is no value in a certain inner SUM query, I need it to return 0, so it doesn't affect the rest of the SUM.

SUM

NULL

total_sum

NULL

SUM

SUM

To make it more clear, let's say I have the following 2 tables:

TABLE1:

ID | NAME | VALUE
1 Name1 1000
2 Name2 2000
3 Name3 3000


TABLE2:

ID | NAME | VALUE
1 Name1 1500
2 Name2 2500
3 Name3 3500


Eventually, the query I need will return a single value - 13500, which is the total sum of all the values under the VALUE column of all the tables here.

VALUE

All the other columns have no meaning for the needed query, and I even don't care much for performance in this case.

union all

3 Answers
3

You can achieve it using Coalesce as follows

SELECT
(SELECT coalesce(SUM(value),0) FROM table1) +
(SELECT coalesce(SUM(value),0) FROM table2) +
(SELECT coalesce(SUM(value),0) FROM table3) as total_sum


Another approach is to use union all to merge all values into single table

union all

select distinct coalesce(sum(a.value), 0) as total_sum from
(select value from table1
union all
select value from table 2
union all
select value from table 3) a;


You can use the ISNULL function to take care of the NULLs.

SELECT ISNULL((
SELECT SUM(value) FROM table1
)
, 0
) + ISNULL((
SELECT SUM(value) FROM table2
)
, 0
) + ISNULL((
SELECT SUM(value) FROM table3
)
, 0
) AS total_sum;


You could simply sum all of them:

select sum(total) as Total
from (
select sum(value) as total from Table1
union all
select sum(value) as total from Table2
union all
select sum(value) as total from Table3
) t;


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.