pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed(no subject)
2+ messages / 2 participants
[nested] [flat]
* (no subject)
@ 2002-11-19 08:09 sun yu <sun.yu@neusoft.com>
2002-11-19 14:26 ` Re: Tomasz Myrta <jasiek@klaster.net>
0 siblings, 1 reply; 2+ messages in thread
From: sun yu @ 2002-11-19 08:09 UTC (permalink / raw)
To: pgsql-sql
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
☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re:
2002-11-19 08:09 (no subject) sun yu <sun.yu@neusoft.com>
@ 2002-11-19 14:26 ` Tomasz Myrta <jasiek@klaster.net>
0 siblings, 0 replies; 2+ messages in thread
From: Tomasz Myrta @ 2002-11-19 14:26 UTC (permalink / raw)
To: sun yu <sun.yu@neusoft.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2002-11-19 14:26 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-11-19 08:09 sun yu <sun.yu@neusoft.com>
2002-11-19 14:26 ` Re: Tomasz Myrta <jasiek@klaster.net>
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