CODEWITHSUNDEEP
sql
SF1
SF1

Reputation: 469

Sum values from different tables

I read some topics about this but I'm not very good with sql. I have 10 tables with these fields:

I want to sum all the value fileds together when they have a specific type. I was trying to do something like this, but it's not working.

select sum(tab1.value) + sum(tab2.value)
from tab1, tab2
where tab1.type = tab2.type = 'box'

I guess I could do many simple queries like these and then sum all the results

select sum(value) from tab1 where type='box'
select sum(value) from tab2 where type='box'

but I wonder if I can do one single query

thanks

Upvotes: 0

Views: 268

Answers (3)

qxg
qxg

Reputation: 7036

1.Use single select;

DECLARE @type NVARCHAR(255) = N'Box';

SELECT (SELECT SUM(value) FROM tab1 WHERE type=@Box)
    + (SELECT SUM(value) FROM tab2 WHERE type=@Box)
    + (SELECT SUM(value) FROM tab3 WHERE type=@Box)
    + (...)

I think it's simplest one.

2.you create a view as

CREATE VIEW tabs
AS
    SELECT value, type FROM tab1
    UNION
    SELECT value, type FROM tab2
    UNION
    SELECT value, type FROM tab3
    UNION
       ...

Then 

SELECT SUM(value) FROM tabs WHERE type = 'BOX'

3.Think why similar column are different tables. Can they be merged into single table? If answer is No, and you have too many tables, consider concatenate SQL strings and use sp_executesql to execute it.

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460288

If the tables are not linked via FK/PK you can use multiple sub-queries:

SELECT (SELECT SUM(tab1.value) FROM tab1 WHERE type='box') as Tab1Sum, 
       (SELECT SUM(tab2.value) FROM tab2 WHERE type='box') as Tab2Sum -- and so on...

This yields a single record where each column is the sum of each table.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Having multiple tables with the same structure is usually a sign of poor database design.

I would suggest that you use your last approach, but put the subqueries in the from clause and then add the results in the select:

select t1.value + t2.value + . . 
from (select sum(value) as value from tab1 where type='box') t1 cross join
     (select sum(value) as value from tab2 where type='box') t2 cross join
     . . .

Alternatively, you could union all them together in the from clause and then take the sum:

select sum(value)
from ((select sum(value) as value from tab1 where type='box') union all
      (select sum(value) as value from tab2 where type='box') union all
      . . .
     ) t;

Upvotes: 1

Related Questions