Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00937: not a single-group group function
ORA-00937: not a single-group group function [message #148122] Tue, 22 November 2005 19:30 Go to next message
kuti36
Messages: 2
Registered: November 2005
Junior Member
Here is a function I have created....

SQL> CREATE OR REPLACE FUNCTION nights_disc_sf
  2  (booking_id IN CHAR)
  3  RETURN NUMBER
  4  IS
  5  roomNo NUMBER(2);
  6  lv_noNigh_num NUMBER(2);
  7  lv_room_price NUMBER(5,2);
  8  lv_booking_disc NUMBER(5,2);
  9  BEGIN
 10   SELECT count(r_room_no),b.noOfNights, rt.price 
 11   INTO roomNo,lv_noNigh_num, lv_room_price
 12   FROM booking b, room r, booking_room br, room_type rt
 13   WHERE booking_id = br.b_booking_id
 14   AND br.r_room_no = r.room_no
 15   AND r.fk1_room_type_code = rt.room_type_code;
 16   
 17  IF lv_noNigh_num > 3 THEN
 18   lv_booking_disc := lv_noNigh_num * lv_room_price/0.1;
 19  
 20  ELSIF lv_noNigh_num > 6 THEN
 21   lv_booking_disc := lv_noNigh_num * lv_room_price/0.2;
 22  
 23  ELSE lv_booking_disc := lv_noNigh_num * lv_room_price;
 24  END IF;
 25  RETURN lv_booking_disc;
 26  END; 
 27  /

Function created.


I am basically trying to calculate the bill for a customer and adding on discounts where applicable.

Here is my problem

SQL> SELECT booking_id,nights_disc_sf(booking_id)
  2  FROM BOOKING
  3  WHERE booking_id = 1;
SELECT booking_id,nights_disc_sf(booking_id)
                  *
ERROR at line 1:
ORA-00937: not a single-group group function
ORA-06512: at "C3053014.NIGHTS_DISC_SF", line 10


When i try to run the function it kicks up this error! I had never seen this error before so I googled it.

This is what i got!

Quote:

ORA-00937: not a single-group group function
Cause: A SELECT list cannot include both a group function, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, and an individual column expression, unless the individual column expression is included in a GROUP BY clause.
Action: Drop either the group function or the individual column expression from the SELECT list or add a GROUP BY clause that includes all individual column expressions listed.



But i don't understand this problem! Please help me by explaing where I have gone wrong. Thanks.

Re: ORA-00937: not a single-group group function [message #148124 is a reply to message #148122] Tue, 22 November 2005 20:10 Go to previous messageGo to next message
rleishman
Messages: 3727
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You need a GROUP BY clause in the SQL in your function. Cut the SQL out of the function and get it running in SQL*Plus. When it works, paste it back into the function.

This seems to be a short-coming in the PL/SQL compiler. Whilst PL/SQL will parse the SQL for syntax and permission errors, it seems that there are a class of errors that are not checked until run-time.

ie. Just because the function compiles doesn't mean it's right.

I learned something here: I would have bet that ORA-00937 would be detected at compile-time.

_____________
Ross Leishman
Re: ORA-00937: not a single-group group function [message #148131 is a reply to message #148122] Tue, 22 November 2005 21:07 Go to previous messageGo to next message
kuti36
Messages: 2
Registered: November 2005
Junior Member
I tried doing that but still can’t get it working??
Any other recomandation????
It’s supposed to be a user defined function.
Please Thank you
Re: ORA-00937: not a single-group group function [message #148136 is a reply to message #148122] Tue, 22 November 2005 21:49 Go to previous messageGo to next message
ndefontenay
Messages: 14
Registered: November 2005
Location: Thailand
Junior Member
The problem is very simple.

You are trying to perform a count without using the GROUP BY function.

This problem comes from the COUNT() function you are using. When you use functions like COUNT(), SUM(), you need to group by all the members included in your SELECT clause

So your answer would be:

SELECT count(r_room_no),b.noOfNights, rt.price 
 11   INTO roomNo,lv_noNigh_num, lv_room_price
 12   FROM booking b, room r, booking_room br, room_type rt
 13   WHERE booking_id = br.b_booking_id
 14   AND br.r_room_no = r.room_no
 15   AND r.fk1_room_type_code = rt.room_type_code
[COLOR=red] 16   GROUP BY r_room_no,b.noOfNights, rt.price[/COLOR]


What this means is that the results will count a r_room_no for every similar No of nights and price couples.
Re: ORA-00937: not a single-group group function [message #148166 is a reply to message #148136] Wed, 23 November 2005 00:04 Go to previous messageGo to next message
lijok
Messages: 68
Registered: April 2005
Member
Quote:

16 GROUP BY r_room_no,b.noOfNights, rt.price


small correction, you need only last two in group by clause.
GROUP BY b.noOfNights, rt.price


-Lijo
Re: ORA-00937: not a single-group group function [message #148180 is a reply to message #148166] Wed, 23 November 2005 00:33 Go to previous messageGo to next message
rleishman
Messages: 3727
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Hmmm. There's an echo in here. I thought that was EXACTLY what I said. Wink
Re: ORA-00937: not a single-group group function [message #148204 is a reply to message #148180] Wed, 23 November 2005 01:24 Go to previous message
ndefontenay
Messages: 14
Registered: November 2005
Location: Thailand
Junior Member
It was exactly what you said but depending of the level of the user it can be hard to understand.

A good example when newbie talks more than technical explanations in my humble opinion.
Previous Topic: delete rows from multiple tables (more than 2 tables)
Next Topic: viewing date in certain format in SQL Plus 8.0
Goto Forum:
  


Current Time: Sat Sep 21 06:17:18 CDT 2019