display the avg salary and loc of each location

Question

Write a query to display the LOC and average salary of Each location.(Scalar Sub query). LOC is in dept table and salary is in emp table. I have to do this with scalar subquery.

select loc,(select avg(sal) from emp) 
from dept group by loc;

Answer 1

Please use below query. You have to join emp and dept table and fetch the results.

Using join:

select d.loc, avg(e.sal) from emp e
inner join dept d
on (e.dept_id = d.dept_id)   -- Changed the column it the foreign key is something else
group by d.loc;

Using sub query:

select d.loc, (select avg(sal) from emp e where d.dept_id = e.dept_id)
from dept d group by d.loc;

Answer 2

If you really have to use scalar subquery then you can use below.

Note: if you need to use GROUP BY you have to use DISTINCT in your SELECT instead.

  WITH DEPT
     AS (SELECT 'TR' AS LOC, 1 DEPTID FROM DUAL
         UNION ALL
         SELECT 'FR' AS LOC, 2 DEPTID FROM DUAL),
     EMP
     AS (SELECT 15 AS SAL, 1 DEPTID FROM DUAL
         UNION ALL
         SELECT 20 AS SAL, 2 DEPTID FROM DUAL
         UNION ALL
         SELECT 35 AS SAL, 1 DEPTID FROM DUAL
         UNION ALL
         SELECT 45 AS SAL, 2 DEPTID FROM DUAL
         )
  SELECT D.LOC,
         (SELECT AVG(SAL)
            FROM EMP E
           WHERE D.DEPTID = E.DEPTID)
    FROM DEPT D

Answer 3

It is not needed to group by loc at the end of the query

select loc,
(select avg(sal) from emp 
where emp.deptno = dept.deptno) avg_sal
from dept;

https://i.stack.imgur.com/f3q28.png