Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
607 views
in Technique[技术] by (71.8m points)

sql - Mysql Query [0001]

I have 2 MySQL and I'm trying to get the number of owners who own at least one dog, for each city. My tables are:

owners
  id, name, city

and

dogs
  id, owner_id, name, weight

I have the following so far:

SELECT owners.city, count(dogs.id) AS 'Owners' 
FROM owners INNER JOIN dogs ON (owners.id = dogs.owner_id) 
GROUP BY owners.city

However, this gives me the total number of dogs per city. But I'd like to get the number of dog owners per city.

How should I do this?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

You have to count only owners id with DISTINCT word, which counts only unique owners.

SELECT owners.city, count(DISTINCT owners.id) AS 'Owners' 
FROM owners INNER JOIN dogs ON (owners.id = dogs.owner_id) 
GROUP BY owners.city

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...