SQL count function not working properly in view

Question

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!

Tim Biegeleisen · Accepted Answer · 2016-09-14 05:56:45Z

1

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)

edited Sep 14, 2016 at 5:56

answered Sep 14, 2016 at 5:44

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dario Ongsono Over a year ago

Thank you for your answer! Yes that works for the final output but my view would require me listing all species and number of animals for each of them, thus i cannot use this method.

Tim Biegeleisen Over a year ago

@DarioOngsono I updated again. Just query the view and add a WHERE condition to restrict to the species with the max number of animals.

Dario Ongsono Over a year ago

Thank you so much! Works great.

Collectives™ on Stack Overflow

SQL count function not working properly in view

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related