SQL QUERY - RETURN COUNTS FROM MULTIPLE TABLES

[barneyii]

I wish to combine three queries involving three tables. For Expamle - Expense Management. We use three tables to record (Hotel, Travel, Entertainment) and capture the employee name, transaction date, and cost. Right now I run three independent queries switching the table name each time (Hotel / Travel / Entertainment).

Select employee,sum(cost),count(*)
from Hotel
where transaction_Date>='2003-01-01'
group by employee
order by 1

Select employee,sum(cost),count(*)
from Travel
where transaction_Date>='2003-01-01'
group by employee
order by 1

Select employee,sum(cost),count(*)
from Entertainment
where transaction_Date>='2003-01-01'
group by employee
order by 1

Can anyone help?

Thanks,
Barneyii

 

[sem]

You may create a view with UNION ALL and select your data from it, but the better way is to switch to PARTITIONS, rather then maintain 3 similar tables. Regards, Dima

 

[barneyii]

Thank you Dima ...

Barney