Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 1C895476420
	for <pgsql-sql@postgresql.org>; Tue, 19 Nov 2002 09:23:36 -0500 (EST)
Received: from serwer.skawsoft.com.pl (unknown [213.25.37.66])
	by postgresql.org (Postfix) with ESMTP id 74874476407
	for <pgsql-sql@postgresql.org>; Tue, 19 Nov 2002 09:23:35 -0500 (EST)
Received: from klaster.net (pe154.krakow.cvx.ppp.tpnet.pl [213.76.40.154])
	by serwer.skawsoft.com.pl (Postfix) with ESMTP
	id 0D11C2BD7B; Tue, 19 Nov 2002 15:11:01 +0100 (CET)
Message-ID: <3DDA4A1C.20804@klaster.net>
Date: Tue, 19 Nov 2002 15:26:36 +0100
From: Tomasz Myrta <jasiek@klaster.net>
User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; PL; rv:1.1) Gecko/20020826
X-Accept-Language: pl, en-us, en
MIME-Version: 1.0
To: sun yu <sun.yu@neusoft.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: 
References: <007e01c28fa3$0bc02c20$a41da8c0@192.168.29.33>
Content-Type: text/plain; charset=GB2312
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200211/273
X-Sequence-Number: 10415

Uz.ytkownik sun yu napisa?:
> HI,What can I do to solve this error;
> I have two tables, as below
> tabel: works
>  
>  empnum | pnum | hours
> --------+------+-------
>  E1     | P1   |    40
>  E1     | P2   |    20
>  E1     | P3   |    80
>  E1     | P4   |    20
>  E1     | P5   |    12
>  E1     | P6   |    12
>  E2     | P1   |    40
>  E2     | P2   |    80
>  E3     | P2   |    20
>  E4     | P2   |    20
>  E4     | P4   |    40
>  E4     | P5   |    80
> (12 rows)
>  
> table:proj
>  pnum |        pname         | ptype  | budget |      city      
> ------+----------------------+--------+--------+-----------------
>  P1   | MXSS                 | Design |  10000 | Deale         
>  P2   | CALM                 | Code   |  30000 | Vienna        
>  P3   | SDP                  | Test   |  30000 | Tampa         
>  P4   | SDP                  | Design |  20000 | Deale         
>  P5   | IRM                  | Test   |  10000 | Vienna        
>  P6   | PAYR                 | Design |  50000 | Deale         
> (6 rows)
>  
>   I want to do this query,but system returns "ERROR:Aggregates not 
> allowd in WHERE clause"
>   please help me,do the query:
>  
>    SELECT PNUM, SUM(HOURS) FROM WORKS
>           GROUP BY PNUM
>          HAVING EXISTS (SELECT PNAME FROM PROJ
>                         WHERE PROJ.PNUM = WORKS.PNUM AND
>                                SUM(WORKS.HOURS) > PROJ.BUDGET / 200);
> I think this query should return two tuples:
> p1/80
> p5/92
Try this:
select pnum, sum(hours) from
 proj join works using (pnum)
group by pnum
having sum(hours)>budget/200;

Regards,
Tomasz Myrta