How to Find Number of Pets Each Person Has-CodePudding

I'm learning MySQL and was having a hard time making a query to find the number of pets each person has. Here are my tables:

CREATE TABLE person(
person_id integer primary key auto_increment,
person_name varchar(500) NOT NULL,
person_birthdate date not null
);

CREATE TABLE pet(
pet_id integer primary key auto_increment,
person_id integer not null,
pet_name varchar(500) not null,
pet_type varchar(500) not null,
Foreign key (person_id) references person (person_id),
Index (person_id)
);

CodePudding user response：

Select person_id,count(person_id) as totalPets from pet group by person_id

Here is your desired query.

CodePudding user response：

For a new answer, join the two tables, group by the person, and the count of entries is the number a person has:

select person_name, count(*) as NumPets
from person p
left join pet pt
  on pt.person_id = p.person_id
group by p.person_id

CodePudding user response：

The number of pets is a simple count, correlated on the person_id, such as:

select person_name, 
  coalesce((select count(*) from pet pt where pt.person_id = p.person_id),0) NumPets
from person p;