ORA-00937 error during select subquery

Question

I am attempting to write a query that returns the the number of employees, the average salary, and the number of employees paid below the average.

The query I have so far is:

select trunc(avg(salary)) "Average Pay", 
count(salary) "Total Employees",
(
   select count(salary)
   from employees 
   where salary < (select avg(salary) from employees)
) UnderPaid
from employees;

But when I run this I get the ora-00937 error in the subquery.

I had thought that maybe the "count" function is what is causing the issue, but even running a simpler sub query such as:

select trunc(avg(salary)) "Average Pay", 
count(salary) "Total Employees",
(
  select avg(salary) from employees 
) UnderPaid
from employees;

still returns the same error. As both AVG and COUNT seem to be aggregate functions, I'm not sure why I'm getting the error?

Thanks

Answer 1

When you use scala subquery, which is a subquery in the select list, it should return only one row. In general, subquery can return multiple rows. So when you use it in the select list with aggregation function, you should wrap it with aggregation function that has no side effect.

select count(*), (select count(*) from emp) from emp
-- ERROR. Oracle doesn't know that the subquery returns only 1 row.

select count(*), max((select count(*) from emp)) from emp
-- You know that the subquery returns 1 row, applying max() results the same.

Or you can rewrite the query like this:

select avg(salary), count(*), count(case when salary < sal_avg then 1 end)
from (select salary, avg(salary) over () sal_avg from emp);