SQL - Using COUNT() as a WHERE condition

Question

I am trying to SELECT the the rows where a certain condition is met multiple times and how many times that condition is met.

For example, for the 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

SELECT every type of animal that has had more than 4 kids at least two times and the number of times this has happened.

Example Output:

Animal   Count
Cow      2

I have tried something along the lines of:

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

But there are obvious errors here with the output (only outputting the animal name instead of the count) and the use of COUNT() as a condition.

Answer 1

You can't use an aggregate (COUNT((NumKids>4)>2)) directly in a WHERE clause, that's what HAVING clauses are for.

Try the following query

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