Daniel:
>>>>
select customer, sum(invoices) from ;
(select Customer, count(InvNum) as Invoices;
from ActiveInvoices;
group by Customer;
union select Customer, count(InvNum) as Invoices;
from HistInvoices;
group by Customer;
order by Customer)
group by customer
>>>>
This does not work because the UNION clause does not work in subqueries...
I don't know of any way to do this in one pass but this works:
Select Customer, count(*) As ActInv, 0 As HisInv ;
From ActiveInvoices ;
Group By Customer ;
Union Select Customer, 0 As ActInv, Count(*) As HisInv ;
From HistInvoices ;
Group By Customer ;
Into Cursor TotInv
Select Customer, Sum(ActInv + HistInv) As InvCount ;
From TotInv ;
Group By Customer
Daniel