Home » SQL & PL/SQL » SQL & PL/SQL » Records count
Records count [message #341380] Mon, 18 August 2008 11:22 Go to next message
cvasi
Messages: 1
Registered: August 2008
Junior Member
Hi all,

I have a small requirement to count the no of records along with the select statement.
I have written the following query

SELECT S.SSTEMPLATE,S.UDC_INSTANCE_C,S.EFF,S.INCCOVDUR,count(*) as reccount
FROM SNC_OUT_SSTEMPLATE S WHERE S.UDC_INSTANCE_C = 'PLEU'

In the database table for the field UDC_INSTANCE_C contains the value PLEU 5 times .
So, the reccount should be 5.

But it is throwing an error as :::
SQL Error: ORA-00937: not a single-group group function

Can anyone help me what iam missing.

Regards,
Chandramouli.
Re: Records count [message #341381 is a reply to message #341380] Mon, 18 August 2008 11:26 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

>Can anyone help me what iam missing.
You are missing the GROUP BY clause
Re: Records count [message #341686 is a reply to message #341380] Tue, 19 August 2008 23:57 Go to previous messageGo to next message
krishnakkk
Messages: 4
Registered: August 2008
Junior Member
hi..chandra mouli
i think this sample query will satisfy your requirement

select count(d1.cname),d2.cname from deposit d1,deposit d2 group by d1.cname;

the query u wrote misses the group by function...

the error u got informs that u cannot retrieve rows from two different tables or a single table if the rows are in equal....

this one is a sample query....u try it with your query but dont miss th group by clause...
Re: Records count [message #341688 is a reply to message #341380] Wed, 20 August 2008 00:00 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
krishnakkk,
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above
Re: Records count [message #341698 is a reply to message #341380] Wed, 20 August 2008 00:36 Go to previous messageGo to next message
cvs_1984
Messages: 136
Registered: August 2007
Location: Punjab, INDIA
Senior Member

Hi Chandramouli,

In Google search you have the answer for your question,


http://www.google.co.in/search?hl=en&q=SQL+Error%3A+ORA-00937%3A+not+a+single-group+group+function&meta=

SELECT S.SSTEMPLATE,S.UDC_INSTANCE_C,S.EFF,S.INCCOVDUR,count(*) as reccount
FROM SNC_OUT_SSTEMPLATE S WHERE S.UDC_INSTANCE_C = 'PLEU'




SELECT S.SSTEMPLATE,S.UDC_INSTANCE_C,S.EFF,S.INCCOVDUR,count(*) as reccount
FROM SNC_OUT_SSTEMPLATE S WHERE S.UDC_INSTANCE_C = 'PLEU' 
Group by S.SSTEMPLATE,S.UDC_INSTANCE_C,S.EFF,S.INCCOVDUR;


Re: Records count [message #341791 is a reply to message #341698] Wed, 20 August 2008 06:01 Go to previous messageGo to next message
sumanthd
Messages: 10
Registered: June 2008
Location: Hyderabad
Junior Member

hi
this quiery will be usefull to u
SELECT S.SSTEMPLATE,S.UDC_INSTANCE_C,S.EFF,S.INCCOVDUR,count(*) as reccount
FROM SNC_OUT_SSTEMPLATE S WHERE S.UDC_INSTANCE_C = 'PLEU'
group by S.SSTEMPLATE,S.UDC_INSTANCE_C,S.EFF,S.INCCOVDUR


regards
sumanth
Re: Records count [message #342473 is a reply to message #341686] Fri, 22 August 2008 07:56 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
krishnakkk wrote on Wed, 20 August 2008 00:57

select count(d1.cname),d2.cname from deposit d1,deposit d2 group by d1.cname;



Three posts now, and not one of them correct, or relevant.

The code above is both invalid and shows disregard for Cartesian products.
Previous Topic: grouping function
Next Topic: Privileges error
Goto Forum:
  


Current Time: Tue Dec 06 12:34:11 CST 2016

Total time taken to generate the page: 0.23086 seconds