Re: Sql query

> HELP!
is this also sql server 7 command? :-)

Igor Neyman, OCP DBA
ineyman_at_perceptron.com   

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Friday, October 04, 2002 11:13 AM

> 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>
>
>
>
>
> ----- Original Message -----
> To: Multiple recipients of list ORACLE-L <mailto:ORACLE-L_at_fatcity.com>
> 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: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
> ----------
> 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: 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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Igor Neyman
  INET: ineyman_at_perceptron.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).