Can SQL calculate aggregate functions across multiple tables?
select owner, sum(num_dogs), sum(num_cats) from (select owner, 1 as num_dogs, 0 as num_cats from dogs union select owner, 0 as num_dogs, 1 as num_cats from cats)group by owner
I prefer this one:
select owner , count(dog_name) dogs , count(cat_name) cats from cats FULL OUTER JOIN dogs ON (cats.owner = dogs.owner)
In T-SQL for SQL Server 2005 (replace the CTE with an inline subquery if not):
WITH ownership AS ( SELECT owner, COUNT(dog_name) AS num_dogs, 0 AS num_cats -- counts all non-NULL dog_name FROM dogs GROUP BY owner UNION SELECT owner, 0 AS num_dogs, COUNT(cat_name) as num_cats -- counts all non-NULL cat_name FROM cats GROUP BY owner)SELECT ownership.owner ,SUM(ownership.num_dogs) AS num_dogs ,SUM(ownership.num_cats) as num_catsFROM ownershipGROUP BY ownership.owner