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) );

Brnaba

First i think you missed a join, second you should get the max as a subquery – Dato

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.

Noseband

why ( where c.salarie_fix = max(c.salaire_fix) )doesn't work or (having c.salarie_fix = max(c.salaire_fix)) work ??? – Brnaba

having works over a group. where is for filtering individual ("input") rows. – Noseband

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.

Finnell

it's too bad c.salarie_fix = max(c.salaire_fix) over () doesn't work – Weimaraner

This option will generally perform better (it avoids a 2nd probe on the table). – Poleyn

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.

Impiety

why ( where c.salarie_fix = max(c.salaire_fix) )doesn't work or (having c.salarie_fix = max(c.salaire_fix)) work ??? – Brnaba

The first one won't work because aggregate functions (and window functions) don't work in the WHERE clause. The second won't work because you can't get MAX(salaire_fix) while grouping by salaire_fix. Really, the best way to achieve the result you want is to use analytic (window) functions as in my answer and @Gordon Linoff's. @Sylvain Leroux's will also work and has the advantage of being completely portable (e.g., to MySQL, which doesn't have window functions). – Impiety

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;

Roo

Answer 5

What I found with Oracle is that Oracle will not comply with group when only one column is required. I use this syntax:

SELECT count(column) as "Sum of Count in column", column from table where column = <some matching criteria> 
group by column order by count(column) DESC

Automatism