Dario Ongsono Dario Ongsono - 1 year ago 73
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!

Answer Source

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)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download