DAN212 DAN212 - 5 months ago 8
SQL Question

SQL - Counting data types and printing highest occurrence first

I am required to produce a list of the different types of pets with a count of how many there are of each ignoring when there is only one type of pet. This should also be listed in descending order. The columns available in my pet table are as follows pet_id, Name, Type, Breed, Gender, Born, owner_id and Notes. But only the Type will be utilised as far as I know.

Below is what I have attempted thus far but it clearly needs improvement and please note this will be executed within Microsoft Access.

SELECT COUNT(Type)
FROM pet
ORDER BY Type DESC;

Answer

You have to GROUP BY the type:

SELECT Type, COUNT(Type)
FROM pet
GROUP BY Type
HAVING COUNT(*) > 1
ORDER BY COUNT(Type) DESC;
Comments