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).
