SQL Query - Distinct results

I have the following two tables:

People [*ID*, Name] 
Pet [*PetID*, OwnerID, Species, Name]

(OwnerID is a foreign key of ID)

I would like the database to list each pers开发者_C百科on and how many different species they own. For example, if Bob (ID 1473) owned a dog, cat and another dog the output should be:

ID    | No. of Species
----------------------
1473  | 2

I realize that this would require correlated sub-queries or outer joins, but I'm not exactly sure how to do that. Any help would be appreciated.

You could use count(distinct ...) for that:

select  People.ID
,       count(distinct Species)
from    People
join    Pet
on      Pet.OwnerID = People.ID
group by
        People.ID

select people.name, count(distinct pet.species)
from people, pet
where people.id = pet.ownerid
group by people.name

try this

Select ID,[No. of Species] from People 
inner join 
  ( select Count(Species) as [No. of Species],OwnerID from Pet  
   group by OwnerID) d 
on Id = d.OwnerID