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

help / color / mirror / Atom feed
From: Tomasz Myrta <jasiek@klaster.net>
To: sun yu <sun.yu@neusoft.com>
Cc: pgsql-sql@postgresql.org
Subject: Re:
Date: Tue, 19 Nov 2002 15:26:36 +0100
Message-ID: <3DDA4A1C.20804@klaster.net> (raw)
References: <007e01c28fa3$0bc02c20$a41da8c0@192.168.29.33>

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





view thread (2+ messages)

Message-ID: <3DDA4A1C.20804@klaster.net>
Permalink:  ../3DDA4A1C.20804@klaster.net/
Also on:    postgresql.org/message-id/3DDA4A1C.20804@klaster.net

 · 

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: jasiek@klaster.net, sun.yu@neusoft.com
  Subject: Re:
  In-Reply-To: <3DDA4A1C.20804@klaster.net>

* 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