ORA-00934 group function is not allowed here is one of the common messages we often get while aggregating data.
Causes of ORA-00934 group function is not allowed here
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
SET SERVEROUTPUT ON SIZE 100000
DECLARE
ipsupply_sequence_number NUMBER;
iprevenue_type_code NUMBER;
ipRECOGNISED NUMBER;
lv_current NUMBER;
ipREVENUE_AMOUNT NUMBER;
lvcount1 number := AMOUNT_RECOGNISED_TO_DATE;
lvcount2 number;
lvcount3 number :=lvcount1-lvcount2;
lv_user_exception EXCEPTION;
————————————–
/*** LIVE ****/
————————————–
PROCEDURE live_status(ipcontract_number IN cm_contract.contract_number%TYPE,
ipcash_flow_amount in CM_CONTRACT_CASHFLOW.cash_flow_amount%TYPE,
ipAMOUNT_RECOGNISED_TO_DATE in cm_contract_rev_balance.AMOUNT_RECOGNISED_TO_DATE%TYPE,
ipREVENUE_AMOUNT in CM_CONTRACT_REVENUE.REVENUE_AMOUNT%TYPE)
IS
BEGIN
SELECT current_version INTO lv_current FROM cm_contract
WHERE country_code = ‘039’
AND company_code = ‘0391’
AND contract_number = ipcontract_number;
SELECT contract_number,sum(REVENUE_AMOUNT) INTO lvcount2 FROM CM_CONTRACT_REVENUE
having country_code = ‘039’
AND company_code = ‘0391’
AND contract_number = ipcontract_number
and version_number=lv_current
and revenue_type_code=23 group by contract_number ;
IF lvcount3 0 then
UPDATE cm_contract_revenue
set REVENUE_AMOUNT = REVENUE_AMOUNT + lvcount3
WHERE country_code = ‘039’
AND company_code = ‘0391’
AND contract_number = ipcontract_number
and version_number = lv_current
and revenue_type_code=23
and REVENUE_DATE = max(REVENUE_DATE);
dbms_output.put_line(SQL%ROWCOUNT ||’ Records got updated’);
else
dbms_output.PUT_LINE (‘not updated’);
end if;
—
END live_status;
—
/******************** Main Block ********************************/
BEGIN
live_status(‘0000000002665008′,’2524′,’4400′,’13.51’);
—
EXCEPTION
WHEN lv_user_exception THEN
ROLLBACK;
WHEN OTHERS THEN
ROLLBACK;
dbms_output.PUT_LINE(‘Error occurred while running script.’
|| SQLERRM ||’Please report to Call Resolver.’);
—
END;
/
i have run this script but still error showing PL/SQL: ORA-00934: group function is not allowed here please help on this to solve
hi
i think this query is giving issues
SELECT contract_number,sum(REVENUE_AMOUNT) INTO lvcount2 FROM CM_CONTRACT_REVENUE
having country_code = ‘039’
AND company_code = ‘0391’
AND contract_number = ipcontract_number
and version_number=lv_current
and revenue_type_code=23 group by contract_number ;
I am not sure why you are using having here