Menu
I have a table which has several one to many relationships with other tables. Let's say the main table is a person, and the other tables represent pets, cars and children. I would like a query that returns details of the person,the number of pets, cars and children they have e.g.
Person.Name Count(cars) Count(children) Count(pets) John Smith 3 2 4 Bob Brown 1 3 0
What is the best way to do this?
272
The T-SQL query below uses the COALESCE() function to iterate through each of the tables to dynamically build a query to capture the row count from each of the tables (individual COUNT queries combined using UNION ALL) and provides the row counts for all the tables in a database.
COUNT () function The SQL COUNT () function returns the number of rows in a table satisfying the criteria specified in the WHERE clause. It sets the number of rows or non NULL column values. COUNT () returns 0 if there were no matching rows.
The most common way to query multiple tables is with a simple SELECT expression. To integrate results from different tables, use the FROM clause to name more than one table. Here’s how it works in practice: Syntax: SELECT table1.column1,table1.column2,table2.column1,....
If the tables (or at least a key column) are of the same type just make the union first and then count. select count(*) from (select tab1key as key from schema.tab1 union all select tab2key as key from schema.tab2 ) Or take your satement and put another sum() around it.
Subquery Factoring (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
Using inline views:
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
you could use the COUNT(distinct x.id) synthax:
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
