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) fromproj join works using (pnum)
group by pnum
having sum(hours)>budget/200;
Regards,
Tomasz Myrta