Union Queries - 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

Union Queries

Author

Topic

rocco2008
Yak Posting Veteran

63 Posts

Posted - 2008-10-31 : 07:40:43

Hi I have two queries and like to sum up the results. My query looks like this

Select Count(*), a, b, AVG(size)
from Table1
where c in ('monday','tuesday','wednesday')
AND ISBN = '12345'
group by a, b
order by a

Select Count(*), a, b, AVG(size)
from Table2
where c in ('monday','tuesday','wednesday')
AND ISBN = '12345'
group by a, b
order by a

results look like this:

69 A 0 33290.333333333336
3593 B 0 5019.2296131366547
1128 A 1 3194.4592198581558

247 A 0 2617.4736842105262
2112 B 0 3525.939393939394
277 A 1 2624.4693140794225

I would now like to sum up the results for A,0 and get it printed out (eg 69 + 247 = 316)

I tried to put a union in between those queries above, but I get an error message. Any ideas?

Tnx

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-31 : 08:26:42

select sum(counting) as counting,a,b,avg_size from
(
Select Count(*) as counting, a, b, AVG(size) as avg_size
from Table1
where c in ('monday','tuesday','wednesday')
AND ISBN = '12345'
group by a, b
union all
Select Count(*), a, b, AVG(size)
from Table2
where c in ('monday','tuesday','wednesday')
AND ISBN = '12345'
group by a, b
) as t
order by a

Madhivanan

Failing to plan is Planning to fail