summ across tables - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

summ across tables

Author

Topic

jjmoss1234
Starting Member

11 Posts

Posted - 2011-09-09 : 05:06:40

I have the same client list in multilple tables (Client column)
each table represents a different type of issue raised and there are 6..

i can use
select client, count(*) FROM table 1 group by client
UNION
select client, count(*) FROM table 2 group by client
UNION
select client, count(*) FROM table 3 group by client etc..

to get a list of totals by client irrespective of type.
Is there an easier way to get just 1 result set per client for all tables?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-09-09 : 05:15:26

use a table variable or a temp table or even a derived table and the sum them at the end.

so:


select client, sum(cnt)
from
(
select client, count(*) cnt FROM table 1 group by client
UNION
select client, count(*) FROM table 2 group by client
UNION
select client, count(*) FROM table 3 group by client etc..
) cnts
group by client

jjmoss1234
Starting Member

11 Posts

Posted - 2011-09-09 : 05:24:56

Thankyou Rick that is perfect!!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources