How count result from 3 tables

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!

Contents hide

Answers:

Method 1

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating