CODEWITHSUNDEEP
mysqljoinsubqueryunion
Jefferson Rosa
Jefferson Rosa

Reputation: 125

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!

Upvotes: 0

Views: 73

Answers (1)

Ankur Kumar
Ankur Kumar

Reputation: 56

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

Upvotes: 2

Related Questions