Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00937: not a single-group group function
ORA-00937: not a single-group group function [message #231296] Mon, 16 April 2007 05:34 Go to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Hi ,

I am getting error in following query

SELECT
distinct cch.pcn_number||CHR(9)||ccaa.business_unit||'-'||ccaa.cisco_log,count(distinct cch.pcn_number) rowcount
FROM
ccm_ccn_header cch,
ccm_ccn_cisco_part_details cccpd,
ccm_ccn_assembly_affected ccaa,
ccm_ccn_case_detail cccd,
ccm_ccn_priority ccp,
ccm_ccn_sample_data ccsd
WHERE
cch.cisco_log = ccaa.cisco_log (+) and
ccaa.case_id = cccd.case_id (+) and
cch.cisco_log = ccp.cisco_log (+) and
cch.cisco_log = cccpd.cisco_log (+) and
cccd.case_id = ccsd.CASE_ID (+) AND
cch.status = 'Open' AND
cch.creation_date >= '02-APR-2006' AND
cch.creation_date <= '16-JAN-2007'

ORA-00937: not a single-group group function

So any one can suggest some other way… I will get the count

--yash


Re: ORA-00937: not a single-group group function [message #231298 is a reply to message #231296] Mon, 16 April 2007 05:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just add the requested and required "group by":
group by cch.pcn_number||CHR(9)||ccaa.business_unit||'-'||ccaa.cisco_log
and remove the distinct.

Regards
Michel



Re: ORA-00937: not a single-group group function [message #231303 is a reply to message #231298] Mon, 16 April 2007 06:08 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Hi Michel,

i tried the same but it is given count bases of group by clause.


let say following query will retune the 19 row

select distinct JOB_ID||PHONE_NUMBER from employee


now if i run this query

select distinct JOB_ID||PHONE_NUMBER ,count(*) from employee group by JOB_ID||PHONE_NUMBER


output come like this

AC_ACCOUNT515.123.8181,1
AC_MGR12121212,1
AD_ASST33333,1

my expected output is as


AC_ACCOUNT515.123.8181,19
AC_MGR12121212,19
AD_ASST33333,19


--Yash
Re: ORA-00937: not a single-group group function [message #231307 is a reply to message #231303] Mon, 16 April 2007 06:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why 19?
You have the result on what you group by.
And remove the distinct!

Regards
Michel
Re: ORA-00937: not a single-group group function [message #231330 is a reply to message #231307] Mon, 16 April 2007 10:09 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member


Hi michel,

Sorry i was not clearly mentioning my requirement .actually I have to send mail to the user if search criteria return more then 10000 records. i have mention the orignal query in my first post. we are taking data in cursor. we used rowcount statement for counting the number of records. if record more then 10000 then system will send mail to user. Now problem is that rowcount statement in cursor depends on fetch statement so unnecessary we are fetching 10000 records. So we suppose to that we can get number of rows also the same query which based on search criteria. I don't want to fire two times the same query one for result set and another for counting the records. plese let me know still I am not able to clear my requirement


--Yash


Re: ORA-00937: not a single-group group function [message #231332 is a reply to message #231330] Mon, 16 April 2007 10:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't understand what you want but this will return the result you gave:
select distinct JOB_ID||PHONE_NUMBER,
       count(distinct JOB_ID||PHONE_NUMBER) over () cnt
from employee

Regards
Michel
Re: ORA-00937: not a single-group group function [message #231415 is a reply to message #231332] Tue, 17 April 2007 00:06 Go to previous message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
THX Michel this query is working fine!!
Previous Topic: DBMS_SHARED_POOL.KEEP
Next Topic: Oracle Driver failing to insert values to CLOB
Goto Forum:
  


Current Time: Fri Dec 09 00:07:12 CST 2016

Total time taken to generate the page: 0.11322 seconds