Group function is not allowed here

Question

When I run the following query, I get

ORA-00934: group function is not allowed here

what is the problem ?

select c.Numcom,c.Nompr,c.salaire_fix
from commercialv c,comercialv c1
where c.salaire_fix=(max(c1.salaire_fix) );

Answer 1

You cannot use an aggregate function in a WHERE clause.

Given your use case, you probably want a subquery:

select c.Numcom,c.Nompr,c.salaire_fix
from commercialv c
where c.salaire_fix=(select max(salaire_fix) from comercialv);

---

The rational is that aggregate functions works on a set. The WHERE clause on the other hand, has only access to the data of one row.

Answer 2

You can do what you want with analytic functions:

select Numcom, Nompr, salair_fix
from (select c.Numcom, c.Nompr, c.salaire_fix,
             max(c.salaire_fix) over () as maxs
      from commercialv c
     ) c
where c.salaire_fix = c.maxs;

As for your query, aggregation functions are not permitted in the where clause.