Select total count of elements group by

I have an Animal Zoo.

Zoo Table

id | name | 

Animal Table

id | zoo_id | name |    type
1     1       Horse      0
2     1       Elephant   0   
3     1       Parrot     1
4     2       Whale      2     
5     2       Fox        0
6     1       Snake      3

I want to select all the zoo data along with the total number of animals of that zoo and all the animal type existing in that zoo

MySQL

SELECT zoo.id as zooid,zoo.name,
(SELECT COUNT(*) FROM animal WHERE animal.zoo_id = zoo.id) as total_animals
 FROM zoo;

But how am i able to get all the animal type existing in that zoo

Expected Result

zooid | name |  total | available_types
 1      USAZOO   4        0 1 3    
 2      UKZOO    2        0 2     

And since i am going to select a lot of Zoo therefore performance matters

Answers


You can use GROUP_CONCAT :

SELECT zoo.id as zooid,zoo.name,
       (SELECT COUNT(*) FROM animal WHERE animal.zoo_id = zoo.id) as total_animals,
       group_concat(type separator ' ')
FROM zoo
GROUP BY zoo.id,zoo.name

I think you need this to the zoo names as well, how did you get your name results? Anyway -

SELECT zoo.id as zooid,
       group_concat(zoo.name separator ' ') as zoo_name,
       (SELECT COUNT(*) FROM animal WHERE animal.zoo_id = zoo.id) as total_animals,
       group_concat(type separator ' ')
FROM zoo
GROUP BY zoo.id

And you should do it with a join:

SELECT zoo.id as zooid,
       group_concat(zoo.name separator ' ') as zoo_name,
       COUNT(*) as total_animals,
       group_concat(type separator ' ')
FROM zoo
INNER JOIN animals
 ON animal.zoo_id = zoo.id
GROUP BY zoo.id

Refining the answer of sagi:

SELECT Zoo.id as zooid, zoo.name AS name,
       COUNT(*) AS total,
       GROUP_CONCAT(DISTINCT type separator ' ') AS available_types
FROM Zoo
INNER JOIN Animal
 ON Animal.zoo_id = zoo.id
GROUP BY zoo.id

This gives you only the distinct types of animals. You can play around with it here: SQLFiddle


Need Your Help

Database encryption

php mysql database encryption

I have a database that contains user details including sensitive data. They're not as sensitive as financial, but they are sensitive nonetheless. The passwords to the accounts are hashed and salted...

jQuery not loading in local wordpress install

jquery wordpress

Can anyone tell why my jQuery script is not loading here