CODEWITHSUNDEEP
mysqlsqlperformanceselectgroup-by
John Doe
John Doe

Reputation: 53

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

Upvotes: 1

Views: 113

Answers (2)

L Kefalas
L Kefalas

Reputation: 63

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

Upvotes: 0

sagi
sagi

Reputation: 40481

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

Upvotes: 1

Related Questions