get sum value from two tables

hi guys,

I have two table name tblhistory and tbltransaction. Both table have column name terminalno and clearingdate. I want get the transaction count based on clearing date and group by terminalno.

here is my coding
select TerminalNo, count (1) as COUNT from tbltransaction where
month(clearingdate) = Month('20110601')
and year(clearingdate) = year ('20110601')
group by terminalno

UNION ALL

select TerminalNo, count(1) as COUNT from tblhistory where
month(clearingdate) = month ('20110601')
and year(clearingdate) = year ('20110601')
group by terminalno
order by terminalno asc

example result from this codes:
Terminalno COUNT
V005------201
V005------298
V007------122
V007------110

BUT

how to get the result like this:
Terminalno COUNT
V005------499
V007------232

really appreciate any help.Thank you

A couple of ways to do this.

But *probably* best performance to do this:

SELECT U.TerminalNo, SUM(U.theCount) AS COUNT
FROM (
select TerminalNo, count (*) as theCount
from tbltransaction
where month(clearingdate) = Month('20110601') and year(clearingdate) = year ('20110601')
group by terminalno
UNION ALL
select TerminalNo, count(*)
from tblhistory
where month(clearingdate) = month ('20110601') and year(clearingdate) = year ('20110601')
group by terminalno
) AS U
GROUP BY terminalno


You COULD do it as

SELECT U.TerminalNo, COUNT(*) AS COUNT
FROM (
select TerminalNo
from tbltransaction
where month(clearingdate) = Month('20110601') and year(clearingdate) = year ('20110601')
group by terminalno
UNION ALL
select TerminalNo
from tblhistory
where month(clearingdate) = month ('20110601') and year(clearingdate) = year ('20110601')
group by terminalno
) AS U
GROUP BY U.TerminalNo

But I think you can see that this UNION produces many more records as the intermediate step.

Still, it might be worth trying it both ways to see which performs better.

thanks old pedant.

the first one get the result as i want.
actually i has tried that similar method but got wrong somewhere.

thanks again. :)