ARE YOU AN IDIOT -----Original Message----- Sent: 04 October 2002 16:13 To: Multiple recipients of list ORACLE-L
HELP! -----Original Message----- Sent: 04 October 2002 14:53 To: Multiple recipients of list ORACLE-L sql server 7??? and you are trying to find an answer on Oracle board? Igor Neyman, OCP DBA [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> ----- Original Message ----- To: Multiple recipients of list ORACLE-L <mailto:[EMAIL PROTECTED]> Sent: Friday, October 04, 2002 2:23 AM Hi naveen, cannot perform an aggregate function on an expression containing an aggregate or subquery is the error i am getting while i am executing the query. i am executing this query in sql server 7 santosh -----Original Message----- [mailto:[EMAIL PROTECTED]]On Behalf Of Naveen Nahata Sent: Thursday, October 03, 2002 9:03 PM To: Multiple recipients of list ORACLE-L Santosh, your query is working. See below SQL> CREATE TABLE CLIENT ( 2 CLIENTID NUMBER PRIMARY KEY, 3 NAME VARCHAR2(10) 4 ); Table created. SQL> CREATE TABLE PROJECT( 2 PROJECTID NUMBER PRIMARY KEY, 3 NAME VARCHAR2(10), 4 CLIENTID REFERENCES CLIENT(CLIENTID) 5 ); Table created. SQL> insert into client values(1, 'Naveen'); 1 row created. SQL> insert into client values(2, 'Santosh'); 1 row created. SQL> insert into project values(1, 'Oracle', 1); 1 row created. SQL> insert into project values(2, 'Java', 1); 1 row created. SQL> insert into project values(3, 'SQL', 2); 1 row created. SQL> commit; Commit complete. SQL> ed Wrote file afiedt.buf 1 SELECT name 2 FROM (SELECT c.name, COUNT(p.clientid) p_count 3 FROM client c, project p 4 WHERE c.clientid = p.clientid 5 GROUP BY c.name) a, 6 (SELECT MAX(COUNT(clientid)) p_max 7 FROM project 8 GROUP BY clientid) b 9* WHERE a.p_count = b.p_max SQL> / NAME ---------- Naveen SQL> I can run your query, then what's the problem? SQL> Regards Naveen -----Original Message----- Sent: Thursday, October 03, 2002 2:53 PM To: Multiple recipients of list ORACLE-L cannot perform an aggregate function on an expression containing an aggregate or subquery is the error i am getting while i am executing the query. SELECT name FROM (SELECT c.name, COUNT(p.clientid) p_count FROM client c, project p WHERE c.clientid = p.clientid GROUP BY c.name) a, (SELECT MAX(COUNT(clientid)) p_max FROM project GROUP BY clientid) b WHERE a.p_count = b.p_max clientid and name are the columns in client table and projectid and clientid are the columns in project table. santosh -----Original Message----- Ignaszak Sent: Monday, September 30, 2002 6:09 PM To: Multiple recipients of list ORACLE-L try it: select name from (select c.name, count(p.id) p_count from clients c, projects p where c.id = p.cl_id group by c.name) a, (select max(count(id)) p_max from projects group by cl_id) b where a.p_count = b.p_max Regards, Leszek At 03:23 2002-09-30 -0800, you wrote: >Hello all, > > I have a query -> >i have 2 tables -> client and project > >fields in project table -> clientid/projectid >fields in client table -> clientid/name > >i want to get the maximum orders one client has got. i mean a project >having the greatest clients >how to write it in single query ?? > > >like >project 1 client 1 >project 2 client 1 >project 3 client 2 > >in the above case, the query should return client ( 1 ). > >Thanks and regards, >Santosh -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Leszek Ignaszak INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Santosh Varma INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas, Kevin INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ********************************************************************** The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. Thank You. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robertson Lee - lerobe INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
