On Wed, 5 May 2004 19:12:00 +0200
Julien Martin <[EMAIL PROTECTED]> wrote:
> Hello,
>
> I have a sql query as follows:
>
> **************************
> SELECT
> DB_ESTABLISHMENT_NAME,
> AVG(DB_GRADE)
>
> FROM
> ESTABLISHMENTS ES LEFT OUTER JOIN GRADES GR ON
> ES.DB_ESTABLISHMENT_ID=GR.DB_ESTABLISHMENT_ID
>
> WHERE
> AVG(DB_GRADE) > 2
>
> GROUP BY
> ES.DB_ESTABLISHMENT_ID
> ;
> **************************
> and I get the following error:
>
> ERROR 1111: Invalid use of group function
>
> Can anyone tell me how to use a function in the where clause?
Filtering rows based on the results of aggregate functions is a job for the HAVING
clause, try:
SELECT
DB_ESTABLISHMENT_NAME,
AVG(DB_GRADE)
FROM
ESTABLISHMENTS ES LEFT OUTER JOIN GRADES GR ON
ES.DB_ESTABLISHMENT_ID=GR.DB_ESTABLISHMENT_ID
GROUP BY
ES.DB_ESTABLISHMENT_ID
HAVING
AVG(DB_GRADE) > 2;
Note that HAVING should be AFTER the GROUP BY, not before it.
Josh
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
