Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00937: not a single-group group function (Oracle 10g, linux)
ORA-00937: not a single-group group function [message #351082] Mon, 29 September 2008 04:24 Go to next message
saramasaeli
Messages: 5
Registered: September 2008
Junior Member
Hi,
This query is supposed to show 5 columns, namely:
Class_Name, Class_Date, Units, CountOfStudentID, Total
but I am getting this error: ORA-00937: not a single-group group function, I do not know how to fix it.

I appriciate if you could help me to solve the problem

Regards,
Sara


SELECT LMS_CLASSES.CLASS_DATE, 
	   LMS_CLASSES.ClASS_NAME, 
	   LMS_CLASSES.UNITS, 
	   COUNT(LMS_STUDENT_CLASS.STAFF_ID)AS "CountOfStudentID", 
	   SUM (LMS_CLASSES.UNITS * COUNT(LMS_STUDENT_CLASS.STAFF_ID)) AS "Total"  					
FROM LMS_CLASSES INNER JOIN LMS_STUDENT_CLASS ON LMS_CLASSES.CLASS_ID=LMS_STUDENT_CLASS.CLASS_ID 
GROUP BY LMS_CLASSES.CLASS_DATE, LMS_CLASSES.CLASS_NAME, LMS_CLASSES.UNITS
Re: ORA-00937: not a single-group group function [message #351088 is a reply to message #351082] Mon, 29 September 2008 04:42 Go to previous messageGo to next message
idris.ali
Messages: 34
Registered: June 2008
Location: Hyderabad
Member
I Think

SUM (LMS_CLASSES.UNITS * COUNT(LMS_STUDENT_CLASS.STAFF_ID))

Should be

SUM (LMS_CLASSES.UNITS) * COUNT(LMS_STUDENT_CLASS.STAFF_ID)


Not sure what is requirement though
Re: ORA-00937: not a single-group group function [message #351091 is a reply to message #351082] Mon, 29 September 2008 04:51 Go to previous messageGo to next message
rajy_salim
Messages: 204
Registered: January 2008
Location: Beirut - Lebanon
Senior Member
You cannot multiply a column value by the count of another column value without a group by.
Re-consider your query.

Rajy
Re: ORA-00937: not a single-group group function [message #351097 is a reply to message #351091] Mon, 29 September 2008 04:59 Go to previous messageGo to next message
saramasaeli
Messages: 5
Registered: September 2008
Junior Member
Actually i tried using Group by but it did no work, can u make a sample for me using my code?

Thanks & Regards
Re: ORA-00937: not a single-group group function [message #351101 is a reply to message #351097] Mon, 29 September 2008 05:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It depends what "Total" field means.
Did you try idris.ali solution?

Regards
Michel
Re: ORA-00937: not a single-group group function [message #351106 is a reply to message #351101] Mon, 29 September 2008 05:13 Go to previous messageGo to next message
saramasaeli
Messages: 5
Registered: September 2008
Junior Member
I tried idris.ali 's solution, in that case the query is working but it doesnt give the expected result
my expected result for total is:
units * CountOfStudentID in each row
then add all together

for example if we have:
units CountOfStudentID Total
2 5 10
2 8 26
1 6 32

it returns Total: 32
Re: ORA-00937: not a single-group group function [message #351114 is a reply to message #351106] Mon, 29 September 2008 06:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So why SUM? Remove it.

Regards
Michel
Re: ORA-00937: not a single-group group function [message #351171 is a reply to message #351106] Mon, 29 September 2008 11:31 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I think you need sum in its analytical version. For more information check about analytic functions in oracle refernce manual.

Regards

Raj
Re: ORA-00937: not a single-group group function [message #351208 is a reply to message #351082] Mon, 29 September 2008 20:25 Go to previous message
saramasaeli
Messages: 5
Registered: September 2008
Junior Member
Thanks for your kind help
I think i found the solution
the query should be like this:
select CLASS_DATE, 
       ClASS_NAME, 
       UNITS,
       stud_num,
       SUM(UNITS*stud_num)
  FROM
       (SELECT LMS_CLASSES.CLASS_DATE, 
	LMS_CLASSES.ClASS_NAME, 
	LMS_CLASSES.UNITS, 
	COUNT(LMS_STUDENT_CLASS.STAFF_ID)AS stud_num 
FROM LMS_CLASSES INNER JOIN LMS_STUDENT_CLASS ON   LMS_CLASSES.CLASS_ID=LMS_STUDENT_CLASS.CLASS_ID 
GROUP BY LMS_CLASSES.CLASS_DATE, LMS_CLASSES.CLASS_NAME, LMS_CLASSES.UNITS) a
GROUP BY CLASS_DATE, CLASS_NAME,UNITS,stud_num


Thanks & Regards
Previous Topic: Another Trigger Issue
Next Topic: Reg the DDL trigger to avoid altering a table (merged)
Goto Forum:
  


Current Time: Sat Dec 10 22:30:39 CST 2016

Total time taken to generate the page: 0.15532 seconds