Sql subquery with a group by and a join on two tables

0
votes

So i have two tables EMPLOYEE- Contains columns including EMPLOYEE_NAME, DEPARTMENT_ID and SALARY DEPARTMENTS - Contains columns including DEPARTMENT_NAME, and DEPARTMENT_ID

I need to display the department name and the average slary for each department and order it by the average salaries.

I am new to DBs and am having trouble. I try to do a subquery in the from field ( this subquery returns exactly what i need minus the department name which requires me to then join the departments table to the results) all the data in the subquery is in one table- employees. while department name is in the departments table.

here is what i tried.

SELECT D.DEPARTMENT_NAME, T.PERDEPT
  FROM
(
 SELECT DEPARTMENT_ID, AVG(SALARY) AS PERDEPT
 FROM EMPLOYEE
 GROUP BY DEPARTMENT_ID
 ORDER BY PERDEPT
) AS TEST T
JOIN DEPARTMENTS
ON D.DEPARTMENT_ID=T.DEPARTMENT_ID;

This returns a

SQL command not properly terminated

on the line with the AS TEST T

any and all help is greatly appreciated many thanks

2
sqloraclejoinsubqueryaggregate
the command didnt format correctly, the line breaks did not show up - newuser9999
just alias table DEPARTMENTS as D - Barbaros Özhan
Side notes: An ORDER BY in a subquery is nothing more than a wish that the optimizer will order your output a certain way. Put ordering on your top-level query, or get unstable output. Several answers are pointing out that you don't even need the subquery - for future askers, that's likely true given the (assumed) data model, but subquery aggregation is generally used to solve duplicate row issues. - Clockwork-Muse

2 Answers

0
votes

This query should do what you ask:

select d.department_name, avg(e.salary) as avg_salary
from salary_department d
left join employee e on e.department_id = d.department_id
group by d.department_name
order by avg(e.salary)
0
votes

Simply correct your table aliases as you seem to have two aliases for subquery (TEST and T) and no assignment for D. Adjust SQL with one alias for each table/query reference:

...
(
 SELECT ...
) AS T
JOIN DEPARTMENTS D

With that said, you do not even need the subquery as aggregate query with JOIN should suffice, assuming DEPARTMENT_ID is unique in DEPARTMENTS table to not double count the aggregate.

SELECT D.DEPARTMENT_NAME, 
       AVG(E.SALARY) AS PERDEPT
FROM EMPLOYEE E
JOIN DEPARTMENTS D
  ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
GROUP BY E.DEPARTMENT_ID, 
         D.DEPARTMENT_NAME
ORDER BY AVG(SALARY)