Invalid Group function

schema:gisq

ORA-00934: group function is not allowed here

Error 1111 Invalid use of group function

Msg 147 An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

ERROR: Aggregates not allowed in WHERE clause

SQL0120N A WHERE clause, GROUP BY clause, SET clause, or SET transition-variable statement contains a column function.

Msg 1013, Ambiguous column name 'name'.

Problem

We want to filter the results returned based on an aggregate function such as COUNT or SUM.

The WHERE clause may not be used for this. The WHERE conditions are considered before the aggregation.

Solutions

• Put the condition into the HAVING clause, after the GROUP BY clause.

SELECT winner FROM nobel
  GROUP BY winner
  HAVING COUNT(winner)>1

SELECT winner FROM nobel
 WHERE COUNT(winner)>1
GROUP BY winner

In MySQL on LINUX the table names are case sensitive. This means that

the table bbc exists but Bbc gives an error. This is a deviation from the SQL standard.