Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00937: not a single-group group function. (Oracle 9i)
ORA-00937: not a single-group group function. [message #281876] Mon, 19 November 2007 16:31 Go to next message
rorey_breaker
Messages: 5
Registered: October 2007
Junior Member
Hello, I am aware that group functions require a group by clause at including all selected fields other than the group funtion.
e.g.
Select number, count(*) from numbers group by number;


Here is the problem...

select
                SubStr(i.old_sys_ref,1,instr(i.old_sys_ref,':',1)),
                e.manager_id,
                AVG(c.credit_limit),
				substr(i.old_sys_ref,1,2)
from
                customer c,
                employee e,
                department d,
                location l,
                job j,
                ph1_item i,
                ph1_cust_details cd,
                ph2_state_region sr
where
                c.state=sr.state
and
                substr(i.old_sys_ref,1,2)=sr.state
and
                sr.regional_group=l.regional_group
and
                l.location_id=d.location_id
and
                d.department_id=e.department_id
and
                j.job_id=e.job_id
and
                j.function='MANAGER'
and
                d.name='SALES'
and
                i.customer_id is null
and
				cd.site_code in
		(
			select distinct 
				site_code
			from
				ph1_cust_details
		)
		
GROUP BY i.old_sys_ref, e.manager_id
/



ERROR at line 2:
ORA-00937: not a single-group group function

My understanding was that by listing the selected fields other that the AVG(..) in the group by would avoid this error.

Any suggestions?

Thank you all!
Re: ORA-00937: not a single-group group function. [message #281877 is a reply to message #281876] Mon, 19 November 2007 16:44 Go to previous messageGo to next message
rorey_breaker
Messages: 5
Registered: October 2007
Junior Member
Embarassed Problem Solved,

I have found the problem, I had an empty line in the script and where I was not executing the script by calling it, but pasting it at the command line it was ignoring the group by expression.

SILLY ME! Embarassed

Thanks for looking though, sorry to be so daft!
Re: ORA-00937: not a single-group group function. [message #281878 is a reply to message #281876] Mon, 19 November 2007 17:09 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
While performance may be OK today, I doubt this statement will scale well as all 7 table in the FROM clause increase in row count.
Only 3 of the 7 tables in the FROM clause contribute data to the SELECT clause.
The remaining 4 table should b eliminated out of the FROM clause
& if they are really needed for filtering rows, they should be subordinated into the WHERE clause.


Previous Topic: Maximum allowed in IN Clause in Select Statement
Next Topic: return comma seperated values
Goto Forum:
  


Current Time: Sat Dec 10 18:17:03 CST 2016

Total time taken to generate the page: 0.07783 seconds