title: Single-row subquery returns more than one row
body: <p>I need some help with oracle sql. The problem: I have 2 tables employee and department. I got the average department salary from one query and i want to use it to see how many employees make more money than the average of their department. I have this so far.</p>
  
  <p>This query returns the avg of the department:</p>
  
  <pre><code>select ROUND(AVG(Salary), 2) Dept_avg_sal
   from employee, department
   where department.department_id = employee.department_id
   group by department_name
  </code></pre>
  
  <p>What i am trying to do is:</p>
  
  <pre><code>select employee_name,
         salary,
         d.department_name
  from   employee e,
         department d
  where  salary &gt; (select ROUND(AVG(Salary), 2) Dept_avg_sal
                   from   employee,
                          department
                   where  department.department_id = employee.department_id
                   group  by department_name)  
  </code></pre>
  
  <p>The error that im getting is :01427. 00000 -  "single-row subquery returns more than one row"</p>
  
  <p>I know that 2 employees in the same department make more money than the average and i think this is what is causing the issue. </p>
  
  <pre><code>EMPLOYEE_NAME       - SALARY -                -DEPARTMENT_NAME-      DEPT_AVG_SAL           
  -------------------- ---------------------- -------------------- ------------ 
  FISHER              - 3000.00  -              SALES      -          2500.00                   
  JONES          -      3000.00               - ACCOUNTING         -  2750.00                   
  KING             -    5000.00           -     EXECUTIVE     -       4500.00                   
  **SCOTT           -     2500.00           -     IT             -      2100.00                   
  SMITH           -     2900.00          -      IT     -              2100.00**                   
  WILSON           -    3000.00          -      RESEARCH      -       2633.33 
  </code></pre>
  
  <p>Any help would be really appreciated.</p>