CODEWITHSUNDEEP
sqloracle-databasesqlplus
user3093583
user3093583

Reputation: 111

Group function is not allowed here

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

Upvotes: 7

Views: 95251

Answers (5)

Daniel Hudsky
Daniel Hudsky

Reputation: 301

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

Upvotes: 0

Kalaiyarasan.A
Kalaiyarasan.A

Reputation: 1

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;

Upvotes: 0

David Faber
David Faber

Reputation: 12485

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.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270371

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.

Upvotes: 3

Sylvain Leroux
Sylvain Leroux

Reputation: 52030

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.

Upvotes: 18

Related Questions