Single-row subquery returns more than one row?

Your initial query is missing any join condition on the outer query and any correlation condition in the inner query that would limit that to just the row for the department of interest. Also generally you do not want to group by name as presumably id is the primary key Resolving these issues to fix your correlated subquery gives SELECT e. Employee_name, e.

Salary, d. Department_name FROM employee e JOIN department d ON d. Department_id = e.

Department_id WHERE e. Salary > (SELECT ROUND(AVG(Salary), 2) Dept_avg_sal FROM employee e2 WHERE e2. Department_id = e.

Department_id) But you may find ditching the scalar correlated sub-query and replacing with a derived table works better SELECT e. Employee_name, e. Salary, d.

Department_name FROM employee e JOIN department d ON d. Department_id = e. Department_id JOIN (SELECT ROUND(AVG(Salary), 2) Dept_avg_sal, department_id FROM employee GROUP BY department_id) e2 ON e2.

Department_id = e. Department_id AND e. Salary > e2.

Dept_avg_sal For Oracle the following should also work I believe SELECT employee_name, salary, d. Department_name FROM (SELECT employee_name, salary, d. Department_name, AVG(Salary) OVER (PARTITION BY e.

Department_id) AS AvgSalary FROM employee e JOIN department d ON d. Department_id = e. Department_id) WHERE salary > AvgSalary.

Your initial query is missing any join condition on the outer query and any correlation condition in the inner query that would limit that to just the row for the department of interest. Also generally you do not want to group by name as presumably id is the primary key. Resolving these issues to fix your correlated subquery gives SELECT e.

Employee_name, e. Salary, d. Department_name FROM employee e JOIN department d ON d.

Department_id = e. Department_id WHERE e. Salary > (SELECT ROUND(AVG(Salary), 2) Dept_avg_sal FROM employee e2 WHERE e2.

Department_id = e. Department_id) But you may find ditching the scalar correlated sub-query and replacing with a derived table works better. SELECT e.

Employee_name, e. Salary, d. Department_name FROM employee e JOIN department d ON d.

Department_id = e. Department_id JOIN (SELECT ROUND(AVG(Salary), 2) Dept_avg_sal, department_id FROM employee GROUP BY department_id) e2 ON e2. Department_id = e.

Department_id AND e. Salary > e2. Dept_avg_sal For Oracle the following should also work I believe SELECT employee_name, salary, d.

Department_name FROM (SELECT employee_name, salary, d. Department_name, AVG(Salary) OVER (PARTITION BY e. Department_id) AS AvgSalary FROM employee e JOIN department d ON d.

Department_id = e. Department_id) WHERE salary > AvgSalary.

The last part worked. – Crematorio Sep 13 at 17:45.

The > operator accepts only one value, thus your inner SELECT has to return exactly 1 row. My guess is that you get multiple rows. Look at what your inner SELECT returns and try LIMIT 1.

I think you should put an extra d. Department_id = department. Department_id condition to the subquery (not tested): select employee_name, salary, d.

Department_name from employee e, department d where salary > (select ROUND(AVG(Salary), 2) Dept_avg_sal from employee, department where department. Department_id = employee. Department_id AND d.

Department_id = department. Department_id group by department_name) Or just write: select e. Employee_name, e.

Salary, d. Department_name from employee e, department d where e. Department_id = d.

Department_id AND salary > (select ROUND(AVG(Salary), 2) Dept_avg_sal from employee where e. Department_id = employee. Department_id).

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.