• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Oracle Sql » ORA-00937: not a single-group group function

ORA-00937: not a single-group group function

September 11, 2022 by techgoeasy Leave a Comment

ORA-00937: not a single-group group function is one of the common messages we often get while aggregating data.

ORA-00937: not a single-group group function

Causes of ORA-00937: not a single-group group function

This error happens when you are using a mixture of individual columns and aggregate functions in the same select statement without using any group by clause with that individual column

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;

select dept_no, avg(salary) from emp_data;
              *
Error at line 1
ORA-00937: not a single-group group function

Checklist to run to resolve ORA-00937: not a single-group group function

(1) Whenever we are using the Individual item and Group function in the same select statement, then we must include the group by clause with that individual column. So Right query for the above error will be

select dept_no,avg(salary) from emp_data group bp dept_no;

(2) we can also resolve this query, if we don’t want by dept_no wise, then this will also run correctly

select avg(salary) from emp_data ;

I hope you like these simple tips on ORA-00937: not a single-group group function

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

Filed Under: Oracle, Oracle Sql

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • How to generate sql monitor report in Oracle
  • How to find the long running (longops session) in Oracle
  • How to find concurrent request id from Sid or spid
  • how to check recycle bin in oracle: Purge, enable/disable
  • how to check fra size in oracle: Location, usage, change size

Copyright © 2023 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us