sql - getting sum of same column from multiple tables

Question

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:

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.

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.

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

I would probably just use union all to stick all those values together, then do the sum over all at the same time. — HoneyBadger
– HoneyBadger, Commented Jul 2, 2018 at 13:20

tourist · Accepted Answer · 2018-07-03 20:06:32Z

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

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;

edited Jul 3, 2018 at 20:06

answered Jul 2, 2018 at 13:21

tourist

4,3537 gold badges28 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

TheCuBeMan Over a year ago

The selected answer here relates to the first part of this answer only, as this solution returns only 1 record with 1 field, holding the total sum, as needed. The solution with the union returns a recordset of several records (one for each selected table and that's not what I need here). Thanks!

Dave.Gugg · Accepted Answer · 2018-07-02 13:20:43Z

0

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;

answered Jul 2, 2018 at 13:20

Dave.Gugg

6,8013 gold badges26 silver badges45 bronze badges

Comments

Cetin Basoz · Accepted Answer · 2018-07-02 13:28:52Z

0

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;

edited Jul 2, 2018 at 13:28

answered Jul 2, 2018 at 13:23

Cetin Basoz

24k3 gold badges34 silver badges48 bronze badges

2 Comments

TheCuBeMan Over a year ago

If I use union all, I get a recordset of several records (one per selected table), and I want a recordset of 1 record only...

Cetin Basoz Over a year ago

@TheCuBeMan, and the code I gave returns just one record. You could try.

Collectives™ on Stack Overflow

sql - getting sum of same column from multiple tables

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related