Home » SQL & PL/SQL » SQL & PL/SQL » Question in group by clause
Question in group by clause [message #193164] Fri, 15 September 2006 02:11 Go to next message
kinjal
Messages: 124
Registered: June 2001
Senior Member
Hi,

I have 3 tables and I want to write the query as below.

select distinct h.pre_hub_count, h.post_hub_count, h.EDI_TXN_NUMBER, h.TRADINGPARTNER,
h.SOURCE_FILENAME,cnt.isa
from MASTER_AUDIT h, ( SELECT txn, sapid,isa, COUNT(*) ct
FROM AUDIT_TABLE_ARCHIVE A,
PHV_OB_EDISAP_XREF B
WHERE DATETIME LIKE '20060914%'
AND A. TRADINGPARTNER = B.EDIID
GROUP BY TXN, SAPID, ISA
ORDER BY TXN, SAPID, COUNT(*)) cnt
where h.tradingpartner = cnt.sapid
and h.BATCH_NUMBER LIKE '20060914%'
GROUP BY h.pre_hub_count, h.post_hub_count,
h.EDI_TXN_NUMBER, h.TRADINGPARTNER, h.SOURCE_FILENAME,cnt.isa;


Now problem here is I want count(*) to be displayed in result. But its giving me some error related to group by clause.
Error looks as below,

ERROR at line 2:
ORA-00979: not a GROUP BY expression

Can anyone help me solve this problem?

Thanks
Re: Question in group by clause [message #193170 is a reply to message #193164] Fri, 15 September 2006 02:34 Go to previous messageGo to next message
Liza79
Messages: 74
Registered: September 2006
Member
the query you have written , does not tend to display the COUNT(*) in the Output. Is this the query which is givig error, or it gives error when you put the COUNT(*) in display??
Re: Question in group by clause [message #193227 is a reply to message #193164] Fri, 15 September 2006 06:40 Go to previous messageGo to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
select distinct h.pre_hub_count, h.post_hub_count, h.EDI_TXN_NUMBER, h.TRADINGPARTNER,
h.SOURCE_FILENAME,cnt.isa
from MASTER_AUDIT h, ( SELECT txn, sapid,isa, COUNT(*) ct
FROM AUDIT_TABLE_ARCHIVE A,
PHV_OB_EDISAP_XREF B
WHERE DATETIME LIKE '20060914%'
AND A. TRADINGPARTNER = B.EDIID
GROUP BY TXN, SAPID, ISA
ORDER BY TXN, SAPID, ISA) cnt
where h.tradingpartner = cnt.sapid
and h.BATCH_NUMBER LIKE '20060914%'
GROUP BY h.pre_hub_count, h.post_hub_count,
h.EDI_TXN_NUMBER, h.TRADINGPARTNER, h.SOURCE_FILENAME,cnt.isa;

i think this should work
Re: Question in group by clause [message #193365 is a reply to message #193227] Fri, 15 September 2006 21:04 Go to previous messageGo to next message
kinjal
Messages: 124
Registered: June 2001
Senior Member
I meant if I put count(*) in outer query it gives error. But I need count(*) in result.
What's the way to display count(*) in result?

Thanks.
Re: Question in group by clause [message #193450 is a reply to message #193164] Sat, 16 September 2006 19:35 Go to previous messageGo to next message
shoblock
Messages: 325
Registered: April 2004
Senior Member
then show the query that has the count(*) and gives the error so we know what you're really doing
Re: Question in group by clause [message #193599 is a reply to message #193365] Mon, 18 September 2006 07:35 Go to previous messageGo to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
select count(*),distinct h.pre_hub_count, h.post_hub_count, h.EDI_TXN_NUMBER, h.TRADINGPARTNER, 
h.SOURCE_FILENAME,cnt.isa 
from MASTER_AUDIT h,( SELECT txn, sapid,isa, COUNT(*) ct
FROM AUDIT_TABLE_ARCHIVE A,
PHV_OB_EDISAP_XREF B
WHERE DATETIME LIKE '20060914%'
AND A. TRADINGPARTNER = B.EDIID
GROUP BY TXN, SAPID, ISA
ORDER BY TXN, SAPID, ISA) cnt
where h.tradingpartner = cnt.sapid
and h.BATCH_NUMBER LIKE '20060914%'
GROUP BY h.pre_hub_count, h.post_hub_count,
h.EDI_TXN_NUMBER, h.TRADINGPARTNER, h.SOURCE_FILENAME,cnt.isa;
Re: Question in group by clause [message #193722 is a reply to message #193164] Tue, 19 September 2006 02:34 Go to previous message
anithalakshmi
Messages: 9
Registered: August 2006
Junior Member
Hi,
can u state the query for which u are getting the error.As u said that u want count(*) in the result but the query u had written doesn't contain count(*) in the outer select clause
Previous Topic: join query
Next Topic: cursor paramater and like clause
Goto Forum:
  


Current Time: Sun Dec 04 00:15:39 CST 2016

Total time taken to generate the page: 0.08773 seconds