pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: sun yu <sun.yu@neusoft.com>
To: pgsql-sql@postgresql.org
Date: Tue, 19 Nov 2002 16:09:55 +0800
Message-ID: <007e01c28fa3$0bc02c20$a41da8c0@192.168.29.33> (raw)

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
☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆
孙雨
沈阳东软中间件技术公司 数据管理事业部
E-mail: sun.yu@neusoft.com
☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆

view thread (2+ messages)  latest in thread

Message-ID: <007e01c28fa3$0bc02c20$a41da8c0@192.168.29.33>
Permalink:  ../007e01c28fa3$0bc02c20$a41da8c0@192.168.29.33/
Also on:    postgresql.org/message-id/007e01c28fa3$0bc02c20$a41da8c0@192.168.29.33

copy link · copy postgr.es

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: sun.yu@neusoft.com
  Subject: Re: 
  In-Reply-To: <007e01c28fa3$0bc02c20$a41da8c0@192.168.29.33>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox