From: | Tomasz Myrta <jasiek(at)klaster(dot)net> |
---|---|
To: | sun yu <sun(dot)yu(at)neusoft(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: |
Date: | 2002-11-19 14:26:36 |
Message-ID: | 3DDA4A1C.20804@klaster.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2002-11-19 15:04:15 | Re: slow group by query |
Previous Message | Tomasz Myrta | 2002-11-19 14:13:13 | Re: create index |