Why should I use aggregators functions with having not where?

Question

Why should I write this:

Having count(id) > 1

instead of this:

Where count(id) > 1

Is this just for English language?

I think if the programmer that invented where clause could made it accept aggregators

Or is there a reason for that? In loading the data or anything like that?

Answer 1

WHERE operates on rows, HAVING operates on collections of rows ("groups"). Aggregate functions cannot be used on single rows, that just would not make any sense.

The MySQL documentation states:

group (aggregate) functions that operate on sets of values [...] If you use a group function in a statement containing no GROUP BY clause, it is equivalent to grouping on all rows.

Answer 2

The reason is that SQL evaluates items in a certain order e.g.

• Identify the tables in question
• Apply the where clause to narrow the rows
• Take values from the columns
• Apply aggregates (group by clauses)
• Apply aggregate limits (having clauses)
• Apply sorting (order by clauses)

If you tried to evaluate an aggregate in a "where" clause it literally wouldn't make sense as it needs to apply the "where" clause before aggregation.