ORA-00934: group function is not allowed here 00934. 00000 - "group function is not allowed here"

Question

The question is: give per office(kantoor) which have the total salary greater that 15000, give the average salary and the office name. use the inner join with using clausule

This is my current query:

SELECT      OFF.OFFICENR,
            OFF.NAME AS OFFICE,
            AVG(SAL)
FROM        OFFICE OFF INNER JOIN EMPLOYEE EMP USING (OFFICENR)
WHERE       SUM((SAL) >= 15000)                 **<---line 29**
GROUP BY    OFF.OFFICENR, OFF.NAME;      

the query output is:

ORA-00934: group function is not allowed here 00934. 00000 - "group function is not allowed here" *Cause:
*Action: Error at Line: 29 Column: 13

Solution

You should use HAVING for this:

SELECT      OFF.OFFICENR,
            OFF.NAME AS OFFICE,
            AVG(SAL)
FROM        OFFICE OFF 
INNER JOIN  EMPLOYEE EMP USING (OFFICENR)
GROUP BY    OFF.OFFICENR, OFF.NAME
HAVING      SUM(SAL) >= 15000;

Comments

[Sourcery]

Could you complement with the sql syntax using your proposed solution.