RE: Sql query

[Santosh Varma]

 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-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Naveen Nahata
Sent: Thursday, October 03, 2002 9:03 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Sql query

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-----
From: Santosh Varma [mailto:[EMAIL PROTECTED]]
Sent: Thursday, October 03, 2002 2:53 PM
To: Multiple recipients of list ORACLE-L
Subject: Sql query

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