SQL - using COUNT () as a WHERE clause

Question

I am trying to SELECT rows where a certain condition occurs several times and how many times this condition is met.

For example, for a table:

Animal    ID    NumKids      PostCode
Cow       1202  5            1405
Cow       3492  6            4392
Chicken   4535  1            2394
Alpaca    2432  0            3453
Cow       2432  3            6253
Chicken   2342  5            4444

CHOOSE all animal species that have had more than 4 children, at least twice and the number of times this has happened.

Result:

Animal   Count
Cow      2

I tried something like:

SELECT  animal
FROM Table
WHERE NumKids>4 AND COUNT((NumKids>4)>2);

But there are obvious errors with the exit (only displaying the name of the animal instead of counting) and using COUNT () as a condition.

Answer 1

You cannot use aggregate ( COUNT((NumKids>4)>2)) directly in a sentence WHERE, which is what the sentences are for HAVING.

Try the following query

select 
  Animal, COUNT(*) AS Count
from Table
where NumKids > 4
group by Animal
having COUNT(*) >= 2