Is the any better way to do sum for multi tables? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Is the any better way to do sum for multi tables?

Author

Topic

latiful
Starting Member

10 Posts

Posted - 2007-03-13 : 07:18:45

-- declare table
declare @Table1 table(ID int, Value int )
-- Insert sample data provided into table
insert into @Table1
select 1 , 2 union all
select 2 , 1 union all
select 7 , 2

-- declare table
declare @Table2 table(ID int, Value int )
-- Insert sample data provided into table
insert into @Table2
select 1 , 2 union all
select 5 , 1 union all
select 3 , 2

-- declare table
declare @Table3 table(ID int, Value int )
-- Insert sample data provided into table
insert into @Table3
select 1 , 2 union all
select 2 , 1 union all
select 3 , 2

-- declare table
declare @Table4 table(ID int, Value int )
-- Insert sample data provided into table
insert into @Table4
select 5 , 2 union all
select 2 , 1 union all
select 10 , 2

/*
Is there anyway I can write one sql query which will give me the following result:
IDs = All unique ID from all tables
SumOfAllValueFromAllTables =for the same ID, value from Table1+for the same ID, value from Table2 + so on ...

IDs - SumOfAllValueFromAllTables
===============================
1 - 6
2 - 3
3 - 4
5 - 3
7 - 2
10 - 2
*/

--I have tried by the following way, but for more tables, it is becoming complicated, because I have 12 tables.

--Is there any better way to do it? Thanks for the help.

select coalesce(t1.ID,t2.ID) as IDs,
coalesce(sum(t1.value),0) +coalesce(sum(t2.value),0) as SumOfAllValueFromAllTables
from @Table1 t1
full join @Table2 t2 on t1.id=t2.id
group by t1.ID,t2.ID
order by IDs

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-13 : 07:57:44

[code]

select ID, sum(Value)
from
(
select ID, Value from @Table1
union all
select ID, Value from @Table2
union all
select ID, Value from @Table3
union all
select ID, Value from @Table4
) a
group by ID
[/code]

KH

latiful
Starting Member

10 Posts

Posted - 2007-03-13 : 19:53:42

Thanks for your VERY GOOD reply.

I have one question about performance. For example, if I have 12 tables and each of them contain 10000 rows, then do you think, by doing the following two queries will create some performance problem?

Thanks for your all VERY GOOD reply.

-- All Test Tables below ------------------

Query 1:

select ID, sum(Value) as 'Value'
from
(
select ID, Value from @Table1
union all
select ID, Value from @Table2
union all
select ID, Value from @Table3
union all
select ID, Value from @Table4
union all
select ID, Value from @TableAll
) a
group by ID order by ID

Query 2:

select 'Total' as Total, sum(V) as 'Total Value'
from (
select ID, sum(Value) as 'V'
from
(
select ID, Value from @Table1
union all
select ID, Value from @Table2
union all
select ID, Value from @Table3
union all
select ID, Value from @Table4
union all
select ID, Value from @TableAll
) a
group by ID
) b

-- Test data ------------------

-- declare table
declare @TableAll table(ID int, Value int )
-- Insert sample data provided into table
insert into @TableAll
select 1 , 0 union all
select 2 , 0 union all
select 3 , 0 union all
select 4 , 0 union all
select 5 , 0 union all
select 6 , 0 union all
select 7 , 0 union all
select 8 , 0 union all
select 9 , 0 union all
select 10 , 0

-- declare table
declare @Table1 table(ID int, Value int )
-- Insert sample data provided into table
insert into @Table1
select 1 , 2 union all
select 2 , 1 union all
select 7 , 2

-- declare table
declare @Table2 table(ID int, Value int )
-- Insert sample data provided into table
insert into @Table2
select 1 , 2 union all
select 5 , 1 union all
select 3 , 2

-- declare table
declare @Table3 table(ID int, Value int )
-- Insert sample data provided into table
insert into @Table3
select 1 , 2 union all
select 2 , 1 union all
select 3 , 2

-- declare table
declare @Table4 table(ID int, Value int )
-- Insert sample data provided into table
insert into @Table4
select 5 , 2 union all
select 2 , 1 union all
select 10 , 2

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 02:01:27

select ID, sum(Value) as [Value]
from
(
select ID, Value from @Table1
union all
select ID, Value from @Table2
union all
select ID, Value from @Table3
union all
select ID, Value from @Table4
union all
select ID, Value from @TableAll
) a
group by ID
with rollup
order by ID

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources