CODEWITHSUNDEEP
sqloracle-database
stefan stefan
stefan stefan

Reputation: 1

SQL Developer - SQL Error: ORA-00909: invalid number of arguments

In HR schema , I wanted to list all the employees that have salary < average salary by departments of different countries , and group them by countries and by department_name 

SELECT E.FIRST_NAME , S.COUNTRY_NAME, S.DEPARTMENT_NAME, S.AVG_SALARY, E.SALARY 
FROM EMPLOYEES E JOIN (SELECT COUNTRY_NAME, DEPARTMENT_NAME, ROUND(AVG(SALARY,0) AS "AVG_SALARY", DEPARTMENT_ID
                       FROM (SELECT SALARY, COUNTRY_NAME, DEPARTMENT_NAME, DEPARTMENT_ID
                             FROM EMPLOYEES JOIN DEPARTMENTS USING (DEPARTMENT_ID)
                             JOIN LOCATIONS USING (LOCATION_ID)
                             JOIN COUNTRIES USING (COUNTRY_ID)
                             )
                       GROUP BY COUNTRY_NAME, DEPARTMENT_NAME, DEPARTMENT_ID
                       )   S ON (E.DEPARTMENT_ID=S.DEPARTMENT_ID)
WHERE E.SALARY< S.AVG_SALARY AND E.DEPARTMENT_ID = S.DEPARTMENT_ID
GROUP BY COUNTRY_NAME, DEPARTMENT_NAME, AVG_SALARY, FIRST_NAME, SALARY
ORDER BY COUNTRY_NAME;

Upvotes: 0

Views: 3985

Answers (1)

Mark Leiber
Mark Leiber

Reputation: 3138

Make sure you match your parentheses.

ROUND(AVG(SALARY,0)

Should be:

ROUND(AVG(SALARY),0)

Upvotes: 2

Related Questions