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