Why the ORA-00979: not a GROUP BY expression Error?-CodePudding

Can someone explain to me how I include all the selects in the group-by without getting an error?

The task I do is:"Show the manager's number and the smallest of his employees! Close all Supervisors who do not have a supervisor. Close all Groups whose minimum salary is less than 1000! Sort the output in descending order Order of salaries" (Google Translate used)

select mgr as Vorgesetztennummer, min(sal) as Mitarbeiter
from emp 
where mgr is not null
group by mgr
having sal>1000
order by sal desc;

This a part of the emp table (just so you can see how it kind looks like):

Error:

ORA-00979: not a GROUP BY expression

Would be nice if someone can explain to me how and why the error happens in the first place.

Thanks in advance.

CodePudding user response：

If you have an GROUP BY attrs clause in SQL then it is not possible to use attributes a ⊄ attrs in SELECT, HAVING, or ORDER BY outside of an aggregate function. In your example sal ⊄ {mgr}, therefore, your query probably should be

select mgr as Vorgesetztennummer, 
       min(sal) as Mitarbeiter
from emp 
where mgr is not null
group by mgr
having min(sal) > 1000
order by min(sal) desc;

CodePudding user response：

select mgr as Vorgesetztennummer, min(sal) as Mitarbeiter
from emp 
where mgr is not null
group by mgr
having min(sal) >1000
order by 2 desc;