Dario Ongsono Dario Ongsono - 2 months ago 17
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.
Code:

CREATE VIEW SpeciesTypes AS
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

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,
       t.number_of_animals
FROM SpeciesTypes t
WHERE t.number_of_animals = (SELECT MAX(number_of_animals) FROM SpeciesTypes)
Comments