| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Sql query
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
ineyman_at_perceptron.com <mailto:ineyman_at_perceptron.com>
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:root_at_fatcity.com]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
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: leszek.ignaszak_at_post.pl 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: ListGuru_at_fatcity.com (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: vsantosh_at_psi.soft.net 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: ListGuru_at_fatcity.com (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: Kevin.Thomas_at_calanais.com 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: ListGuru_at_fatcity.com (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: lerobe_at_acxiom.co.uk 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: ListGuru_at_fatcity.com (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).Received on Fri Oct 04 2002 - 11:05:02 CDT
 |
 |