Обсуждение:

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