Dario Ongsono Dario Ongsono - 1 month ago 10
MySQL Question

SQL count function not working properly in view

I've created a view to query on but does not seem to be working properly.

SELECT species.Name, count(animal.animalID) as number_of_animals
FROM zoo.species, zoo.animal
WHERE species.speciesID = animal.speciesID
GROUP by Name

This code shows each species name and the number of animals within each species. However, i am trying to write a query in finding out which species has the most number of animals including the species name.

The query that i used was:

SELECT name, max(number_of_animals)
FROM speciestypes;

This showed me correct highest number of animals but the species name does not change.. does anyone know how to fix this? Thanks!


If you want to get the name of the species having the greatest number of animals, using your view you can just use a WHERE clause to restrict the result set.

SELECT t.name,
FROM SpeciesTypes t
WHERE t.number_of_animals = (SELECT MAX(number_of_animals) FROM SpeciesTypes)