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.

Answer 3

You could also do this query using MAX() as a window function (or analytic function if you prefer the Oracle lingo):

SELECT numcom, nompr, salaire_fix FROM (
    SELECT numcom, nompr, salaire_fix, MAX(salaire_fix) OVER ( ) AS max_salaire_fix
      FROM commercialv
) WHERE salaire_fix = max_salaire_fix;

You could also use RANK():

SELECT numcom, nompr, salaire_fix FROM (
    SELECT numcom, nompr, salaire_fix, RANK() OVER ( ORDER BY salaire_fix DESC ) AS salaire_fix_rank
      FROM commercialv
) WHERE salaire_fix_rank = 1;

Or even ROWNUM:

SELECT * FROM (
    SELECT numcom, nompr, salaire_fix
      FROM commercialv
     ORDER BY salaire_fix DESC
) WHERE rownum = 1;

The only difficulty with the last is that it will get only one row even if there are additional rows with the maximum value of salaire_fix. The first two queries will get more than one row in that case.

Answer 4

You can't use group function in where clause so you can use having clause. Example:

SELECT DEPTNO,COUNT(*)
FROM EMP
GROUP BY DEPTNO
HAVING COUNT(*) >= 2;