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.
Learn the cause and how to resolve the ORA-00934 error message in Oracle.
Description
When you encounter an ORA-00934 error, the following error message will appear:
- ORA-00934: group function is not allowed here
Resolution
The option(s) to resolve this Oracle error are:
Option #1
Try removing the group function from the WHERE clause or GROUP BY clause. If required, you can move the group function to the HAVING clause.
For example, if you tried to execute the following SQL statement:
SELECT department, SUM(sales) AS "Total sales" FROM order_details WHERE SUM(sales) > 1000 GROUP BY department;
You would receive the following error message:
You could correct this statement by using the HAVING clause as follows:
SELECT department, SUM(sales) AS "Total sales" FROM order_details GROUP BY department HAVING SUM(sales) > 1000;
Option #2
You could also try moving the group by function to a SQL subquery.
For example, if you tried to execute the following SQL statement:
SELECT department, SUM(sales) AS "Total sales" FROM order_details WHERE SUM(sales) > 1000 GROUP BY department;
You would receive the following error message:
You could correct this statement by using a subquery as follows:
SELECT order_details.department,
SUM(order_details.sales) AS "Total sales"
FROM order_details, (SELECT department, SUM(sales) AS "Sales_compare"
FROM order_details
GROUP BY department) subquery1
WHERE order_details.department = subquery1.department
AND subquery1.Sales_compare > 1000
GROUP BY order_details.department;
Oracle PL/SQL error message: ORA-00934: group function is not allowed here.
Cause:
One of the group functions, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, was used in a WHERE or GROUP BY clause.
Solution:
Remove the group function from the WHERE or GROUP BY clause. The desired result may be achieved by including the function in a subquery or HAVING clause.
Example:
select s.student_id, s.first_name, s.last_name, count(o.order_id) from students s, orders o where s.student_id = o.student_id group by s.student_id, s.first_name, s.last_name, count(o.order_id) order by s.student_id;
Output:
ORA-00934: group function is not allowed here
Correct:
select s.student_id, s.first_name, s.last_name, count(o.order_id) from students s, orders o where s.student_id = o.student_id group by s.student_id, s.first_name, s.last_name order by s.student_id;
ORA-00934
ORA-00934: групповая функция здесь не разрешена
Причина:
Групповые функции такие, как (AVG, COUNT, MAX, MIN, SUM, STDDEV, VARIANCE) использовались в WHERE или GROUP BY предложении.
Действие:
Удалите групповую функцию из WHERE или GROUP BY предложения. Вы можете достичь желаемого действия включением функции в подзапрос или HAVING предложение.
ORA-00934 group function is not allowed here is one of the common messages we often get while aggregating data.
This error happens when you are trying to use the where clause to restrict groups
CREATE TABLE "EMP_DATA"
( "EMP_NO" NUMBER(4,0),
"EMP_NAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SALARY" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPT_NO" NUMBER(2,0)
);
insert into emp_data values( 1000, 'BILL', 'MANAGER', 7839, to_date('1-5-2007','dd-mm-yyyy'), 2850, null, 10 );
insert into emp_data values( 1001, 'MATT', 'MANAGER', 7839, to_date('9-6-2008','dd-mm-yyyy'), 2450, null, 10 );
insert into emp_data values( 1002, 'SCOTT', 'ANALYST', 7566, to_date('9-6-2012','dd-mm-yyyy'), 3000, null, 20 );
insert into emp_data values( 1003, 'ARNOLD', 'ANALYST', 7566, to_date('9-6-2017','dd-mm-yyyy'), 3000, null, 20 );
insert into emp_data values( 1004, 'BANNER', 'ANALYST', 7567, to_date('9-7-2017','dd-mm-yyyy'), 4000, null, 20 );
insert into emp_data values( 1005, 'TOM', 'ANALYST', 7567, to_date('9-7-2017','dd-mm-yyyy'), 4000, null, 30 );
commit;
select dept_no,avg(salary) from emp_data where avg(salary) > 1000 group by dept_no;
select dept_no,avg(salary) from emp_data where avg(salary) > 1000;
*
Error at line 1
ORA-00934: group function is not allowed here
Checklist to run to resolve the ORA-00934 group function is not allowed here
This error happens when you are trying to use the where clause to restrict groups. The right way to restrict groups is by Having clause. So the correct query is
select dept_no,avg(salary) from emp_data having avg(salary) > 1000 group by dept_no;
Let’s see some more examples
(1) select dept_no, count(*) from emp_data where count(*) > 1 group by dept_no; Error at line 1 ORA-00934: group function is not allowed here. The correct way is select dept_no, count(*) from emp_data having count(*) > 1 group by dept_no;
(2)select emp_no,salary from emp_data where salary=max(salary); Error at line 1 ORA-00934: group function is not allowed here. The correct way is select emp_no,salary from emp_data where salary=(select max(salary) from emp_data);
I hope you like these simple tips on ORA-00934 group function is not allowed here
Related Articles
not a group by expression : Check out method to resolve ORA-00979: not a group by expression. This error happens when you are using group functions
Analytic functions in oracle : Oracle Analytic functions compute an aggregate value based on group of rows by using over partition by oracle clause , they differ from aggregate functions
rank in oracle : RANK, DENSE_RANK and ROW_NUMBER are oracle analytical function which are used to rank rows in the group of rows called window
Lead function in oracle : Check out LAG function in Oracle & Lead function in Oracle, how to use them in analytic queries and how it works in Oracle sql
Top-N Queries in Oracle : Check out this page on exploring the various ways to achieve Top-N Queries in Oracle like oracle top 100, selecting top 10 rows in oracle
Auto Increment in Oracle : Check out how to use Auto Increment in Oracle before 12c version and with 12 and above version with detailed examples
group by oracle : GROUP BY oracle is used to group rows into a set of summary rows by values of single or multiple columns. Group by oracle order by
https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqlj32654.html