SQL Query to Count () múltiples tablas

Factorización de subconsulta (9i +):

WITH count_cars AS ( SELECT t.person_id COUNT(*) num_cars FROM CARS c GROUP BY t.person_id), count_children AS ( SELECT t.person_id COUNT(*) num_children FROM CHILDREN c GROUP BY t.person_id), count_pets AS ( SELECT p.person_id COUNT(*) num_pets FROM PETS p GROUP BY p.person_id) SELECT t.name, NVL(cars.num_cars, 0) 'Count(cars)', NVL(children.num_children, 0) 'Count(children)', NVL(pets.num_pets, 0) 'Count(pets)' FROM PERSONS t LEFT JOIN count_cars cars ON cars.person_id = t.person_id LEFT JOIN count_children children ON children.person_id = t.person_id LEFT JOIN count_pets pets ON pets.person_id = t.person_id 

Usando vistas en línea:

  SELECT t.name, NVL(cars.num_cars, 0) 'Count(cars)', NVL(children.num_children, 0) 'Count(children)', NVL(pets.num_pets, 0) 'Count(pets)' FROM PERSONS t LEFT JOIN (SELECT t.person_id COUNT(*) num_cars FROM CARS c GROUP BY t.person_id) cars ON cars.person_id = t.person_id LEFT JOIN (SELECT t.person_id COUNT(*) num_children FROM CHILDREN c GROUP BY t.person_id) children ON children.person_id = t.person_id LEFT JOIN (SELECT p.person_id COUNT(*) num_pets FROM PETS p GROUP BY p.person_id) pets ON pets.person_id = t.person_id 

puedes usar la COUNT(distinct x.id) :

 SELECT person.name, COUNT(DISTINCT car.id) cars, COUNT(DISTINCT child.id) children, COUNT(DISTINCT pet.id) pets FROM person LEFT JOIN car ON (person.id = car.person_id) LEFT JOIN child ON (person.id = child.person_id) LEFT JOIN pet ON (person.id = pet.person_id) GROUP BY person.name 

Probablemente lo haría así:

 SELECT Name, PersonCars.num, PersonChildren.num, PersonPets.num FROM Person p LEFT JOIN ( SELECT PersonID, COUNT(*) as num FROM Person INNER JOIN Cars ON Cars.PersonID = Person.PersonID GROUP BY Person.PersonID ) PersonCars ON PersonCars.PersonID = p.PersonID LEFT JOIN ( SELECT PersonID, COUNT(*) as num FROM Person INNER JOIN Children ON Children.PersonID = Person.PersonID GROUP BY Person.PersonID ) PersonChildren ON PersonChildren.PersonID = p.PersonID LEFT JOIN ( SELECT PersonID, COUNT(*) as num FROM Person INNER JOIN Pets ON Pets.PersonID = Person.PersonID GROUP BY Person.PersonID ) PersonPets ON PersonPets.PersonID = p.PersonID 

Tenga en count que depende de su sabor de RDBMS, si admite selects anidadas como las siguientes:

 SELECT p.name AS name , (SELECT COUNT(*) FROM pets e WHERE e.owner_id = p.id) AS pet_count , (SELECT COUNT(*) FROM cars c WHERE c.owner_id = p.id) AS world_pollution_increment_device_count , (SELECT COUNT(*) FROM child h WHERE h.parent_id = p.id) AS world_population_increment FROM person p ORDER BY p.name 

IIRC, esto funciona al less con PostgreSQL y MSSQL. No probado, por lo que su kilometraje puede variar.

Usar subselects no es una buena práctica, pero puede estar aquí, será bueno

 select p.nombre, (select conteo (0) de autos c donde c.idperson = p.idperson), 
                (select recuento (0) de los niños ch donde ch.idperson = p.idperson),
                (select conteo (0) de mascotas pt donde pt.idperson = p.idperson)
   de la persona p

Puedes hacer esto con tres uniones externas:

 SELECT Person.Name, sum(case when cars.id is not null then 1 else 0 end) car_count, sum(case when children.id is not null then 1 else 0 end) child_count, sum(case when pets.id is not null then 1 else 0 end) pet_count FROM Person LEFT OUTER JOIN cars on Person.id = cars.person_id LEFT OUTER JOIN children on Person.id = children.person_id LEFT OUTER JOIN pets on Person.id = pets.person_id GROUP BY Person.Name 

Creo que Oracle ahora admite el case when syntax, pero si no, podrías usar un decodificador.

Tendría que include múltiples declaraciones de conteo en la consulta. La parte superior de mi cabeza,

 SELECT p.Name, COUNT(DISTINCT t.Cars), COUNT(DISTINCT o.Children), Count(DISTINCT p.Pets) FROM Person p INNER JOIN Transport t ON p.ID = t.PersonID LEFT JOIN Offspring o ON p.ID = o.PersonID LEFT JOIN Pets p ON p.ID = o.OwnerID GROUP BY p.Name ORDER BY p.Name