tipos - funciones de agrupamiento mysql

¿Puede SQL calcular funciones agregadas en múltiples tablas?

Question

Digamos que tengo dos tablas existentes, "perros" y "gatos":

 dog_name | owner
 ---------+------
 Sparky   | Bob
 Rover    | Bob
 Snoopy   | Chuck
 Odie     | Jon

 cat_name | owner
 ---------+------
 Garfield | Jon
 Muffy    | Sam
 Stupid   | Bob

¿Cómo escribo una consulta con esta salida?

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

Answer 1

En T-SQL para SQL Server 2005 (reemplace el CTE con una subconsulta en línea si no es así):

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 2

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 3

Prefiero éste:

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

Answer 4

Comencé con la excelente respuesta de Cade Roux, pero cambié el WITH ... AS () para usar una variable de tabla, ya que terminé usando los resultados de una consulta similar para otras funciones agregadas.

-- 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