OraFAQ Forum: SQL & PL/SQL » ORA-00937: not a single-group group function

Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00937: not a single-group group function

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-00937: not a single-group group function [message #20100]

Tue, 30 April 2002 02:24

Mosquito
Messages: 6
Registered: April 2002

Junior Member

Greetings,

I made this select for a cursor in a PL/SQL procedure. It's supposed to select how many hours an employee has worked at a given project (plus some project information), from a given time on. "project" and "fromDate" are the parameters to the procedure (a varchar and a date, respectively).

The "sum" function is supposed to sum up the difference of the dates (when the employee started working and when he ended work), in hours.

Have a look:

select T.sNode --project identifier
,T.dDate --the date at which the employee worked
,U.sUserName --employees name
,sum(round((T.endDate - T.startDate) * 24,2)) as hrs --the time the employee worked (the date is the same as dDate, but is not used)
,T.sActivity --project info
,T.sPreActivity --project info
,T.iFactor --project info
from time_table T
,user_table U
where (T.nUserId=U.nUserId
and T.dDate > fromDate
and T.sNode like '%' || project || '%');

Unfortunately, this select produces an
"ORA-00937: not a single-group group function" error.

The select works, if the "sum" function is removed.

I would appreciate any insights on this problem.
Thanks
-Mosquito

Report message to a moderator

Re: ORA-00937: not a single-group group function [message #20105 is a reply to message #20100]

Tue, 30 April 2002 02:57

Rico
Messages: 7
Registered: April 2002

Junior Member

You have an Aggregation in your select. You have to make a goup by clause!!!

select T.sNode --project identifier
,T.dDate --the date at which the employee worked
,U.sUserName --employees name
,sum(round((T.endDate - T.startDate) * 24,2)) as hrs --the time the employee worked (the date is the same as dDate, but is not used)
,T.sActivity --project info
,T.sPreActivity --project info
,T.iFactor --project info
from time_table T
,user_table U
where (T.nUserId=U.nUserId
and T.dDate > fromDate
and T.sNode like '%' || project || '%')
Group by T.sNode , T.dDate, U.sUserName;

In my opinion is that the failure.

Report message to a moderator

Hope this is an improvement [message #20109 is a reply to message #20105]

Tue, 30 April 2002 03:16

Mosquito
Messages: 6
Registered: April 2002

Junior Member

Hi, thanks for your help, but I'm afraid you only changed my error message into:
ORA-00979: not a GROUP BY statement

I'll experiment a bit with grouping, but let me know, if you have another idea.
(I know, "experimenting" is not exactly what programming is about, but thats sorta the way I learn all this stuff)

Report message to a moderator

Re: ORA-00937: not a single-group group function [message #20149 is a reply to message #20105]

Wed, 01 May 2002 13:50

Alex Mazur
Messages: 17
Registered: March 2002

Junior Member

select T.sNode,T.dDate,U.sUserName,
sum(round((T.endDate - T.startDate)*24,2)),
T.sActivity, T.sPreActivity,T.iFactor --project info
from time_table T,user_table U
where (T.nUserId=U.nUserId
and T.dDate > fromDate
and T.sNode like '%' || project || '%')
Group by T.sNode,T.dDate,U.sUserName,T.sActivity, T.sPreActivity,T.iFactor

Report message to a moderator

Previous Topic:	This can't be right......
Next Topic:	Sql statements -

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 04 06:42:28 CDT 2024