How count result from 3 tables-CodePudding

Please, take a look in these 3 tables:

Pets
 ---- --------- ------- 
| id | petname | owner |
 ==== ========= ======= 
| 1  | chew    | 1     |
 ---- --------- ------- 
| 2  | yo      | 2     |
 ---- --------- ------- 
| 3  | mah     | 3     |
 ---- --------- ------- 

Owners
 ---- ------- ----------- 
| id | store | ownername |
 ==== ======= =========== 
| 1  | 1     | Jonh      |
 ---- ------- ----------- 
| 2  | 2     | Joe       |
 ---- ------- ----------- 
| 3  | 3     | Smith     |
 ---- ------- ----------- 

Stores
 ---- ------------ 
| id | storename  |
 ==== ============ 
| 1  | Lite Store |
 ---- ------------ 
| 2  | Mega       |
 ---- ------------ 
| 3  | Corner     |
 ---- ------------

Is this possible to get this result?

 ------------ ------------ 
| storename  | Total Pets |
 ============ ============ 
| Lite Store | 5          |
 ------------ ------------ 
| Mega       | 8          |
 ------------ ------------ 
| Corner     | 0          |
 ------------ ------------

I tried for hours a lot of subqueries and joins, but I'm missing something, maybe Union?

With below I got close, but still far

SELECT storename, COUNT(distinct stores.storename) as store, count(DISTINCT pets.owner) as petowner from stores inner join owners on owners.id = stores.id inner JOIN pets on pets.owner = stores.id group by stores.id
SELECT stores.storename, COUNT(distinct stores.storename) as store, count(DISTINCT pets.owner) as petowner from stores inner join owners on owners.id = stores.id inner JOIN pets on pets.owner = stores.id group by stores.id
SELECT stores.storename, COUNT(distinct owners.id) as store, count(DISTINCT pets.owner) as petowner from stores inner join owners on owners.id = stores.id inner JOIN pets on pets.owner = stores.id group by stores.id
SELECT COUNT(*),(SELECT COUNT(*) from stores) FROM pets
SELECT COUNT(*),(SELECT DISTINCT(COUNT(*)) from stores),(SELECT DISTINCT(COUNT(*)) FROM owners) FROM pets
SELECT DISTINCT(COUNT(*)), ( select count(DISTINCT(stores.storename)) from stores join owners on stores.id = stores.storename ) FROM pets
select stores.storename, (select count(*) from pets) from stores join owners on stores.id = stores.storename group by storename
select DISTINCT(stores.storename), (select count(*) from pets) from stores join owners on stores.id = stores.storename group by storename
select (count(stores.storename)), (select count(*) from pets) as total from stores join owners on stores.id = stores.storename group by storename

Is there any way to get the result above?

Any help will be great appreciated!

CodePudding user response：

Thank you for providing your queries. I have formed a below query. Request you to try it and see if it works. if not, let me know

Select s.storename, count(p.petname) from pets p join owners o on p.owner=o.id join stores s on o.store=s.id group by 1