count on multiple tables

[underm...@gmail.com]

Hi,

I'm trying to do a count statement on multiple tables that have
different sets of data, but a common key.
As an example, Table1 has EmployeeNumber and InvoiceNumber as two
fields. Tables2 has EmployeeNumber and ClaimNumber.
Assume that the same employee can have multiple invoices as well as
multiple claims.

I would like a query which would return the data in the format:
EmployeeNumber, Total Invoices, Total Claims

Any help?

[Ed Murphy]

underm...@gmail.com wrote:

select coalesce(t1c.EmployeeNumber,t2c.EmployeeNumber) EmployeeNumber,
coalesce(t1c.TotalInvoices,0) TotalInvoices,
coalesce(t2c.TotalClaims,0) TotalClaims
from (
select EmployeeNumber, count(*) TotalInvoices
from Table1
group by EmployeeNumber
) t1c full outer join (
select EmployeeNumber, count(*) TotalClaims
from Table2
group by EmployeeNumber
) t2c on t1c.EmployeeNumber = t2c.EmployeeNumber

Or, if you want to include all employees (even if both counts are zero):

select e.EmployeeNumber,
coalesce(t1c.TotalInvoices,0) TotalInvoices,
coalesce(t2c.TotalClaims,0) TotalClaims
from Employees e
left outer join (
select EmployeeNumber, count(*) TotalInvoices
from Table1
group by EmployeeNumber
) t1c on e.EmployeeNumber = t1c.EmployeeNumber
left outer join (
select EmployeeNumber, count(*) TotalClaims
from Table2
group by EmployeeNumber
) t2c on e.EmployeeNumber = t2c.EmployeeNumber