Julien Martin <[EMAIL PROTECTED]> wrote:
>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?
You need to use HAVING instead of WHERE:
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
;
- seb
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
