sql - Mysql Query [0001]

Question

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?

Answer 1

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