Can SQL compute aggregate functions across multiple tables?

Question

Say I have two existing tables: “dogs” and “cats”:

dog_name | owner ---------+------ Sparky | Bob Rover | Bob Snoopy | Chuck Odie | Jon cat_name | owner ---------+------ Garfield | Jon Muffy | Sam Stupid | Bob 

How to write a request with this output?

  owner | num_dogs | num_cats ------+----------+--------- Bob | 2 | 1 Chuck | 1 | 0 Sam | 0 | 1 Jon | 1 | 1 

Answer 1

 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 

Answer 2

I prefer this:

 select owner , count(dog_name) dogs , count(cat_name) cats from cats FULL OUTER JOIN dogs ON (cats.owner = dogs.owner) 

Answer 3

In T-SQL for SQL Server 2005 (replace CTE with the built-in 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_cats FROM ownership GROUP BY ownership.owner 

Answer 4

I started with Cade Roux's excellent answer, but modified WITH ... AS () to use the table variable since I ended up using the results of a similar query for additional aggregate functions.

 -- Table variable declaration DECLARE @RainingCatsDogs TABLE ( Owner nvarchar(255), num_cats int, num_dogs int ) -- Populate the table variable with data from the union of the two SELECT statements INSERT INTO @RainingCatsDogs -- Get the count of doggies SELECT owner, COUNT(dog_name) AS num_dogs, 0 AS num_cats FROM dogs GROUP BY owner -- join the results from the two SELECT statements UNION -- Get the count of kittehs SELECT owner, 0 AS num_dogs, COUNT(cat_name) as num_cats FROM cats GROUP BY owner -- From the table variable, you can calculate the summed results SELECT owner, SUM(num_dogs), SUM(num_cats) FROM @RainingCatsDogs