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
