sorry, I messed up. Actually the query works. but how can I put
the quota within the select? is it possible?
select rep, sum(amount), quota
from orders, salesreps
where empl_num = rep
group by rep
having sum(amount) < (select quota from salesreps where empl_num = rep);
the error:
ERROR: Illegal use of aggregates or non-group column in target list
I know that for one table, with "group", select can only have the grouping
non-aggregate attributes, and it makes sense. However, for two tables,
the constraint does not make sense--i.e., it is required in many
situations. Here, every sales-representative has and only has one
quota. so, it is quite reasonable to select both.
Is there any work arounds except making two queries?
thanks
On Wed, 15 Dec 1999 kaiq@realtyideas.com wrote:
> hi, can having clause have a sub select?
> here is a example that I abstracted and I played with.
> it is the "classic" example:
> offices, salesreps, products, customers, orders
> we just use salesreps and orders:
> ---------------------------------------------------------
> create table salesreps (
> empl_num integer not null,
> name varchar (15) not null,
> age integer,
> rep_office integer,
> title varchar (10),
> hiredate date not null,
> manager integer,
> quota money,
> sales money not null
> );
>
> create table orders (
> order_num integer not null,
> order_date date not null,
> cust integer not null,
> rep integer,
> mfr char(3) not null,
> product char(5) not null,
> qty integer not null,
> amount money not null
> );
>
> select rep, sum(amount)
> from orders
> group by rep
> having sum(amount) < (select quota from salesreps where empl_num = rep);
> -------------------------------------------------------------
> the ideas is to get the sales amount of sales-representives that
> sales less than his/her quota.
>
> for the query, I got (I'm using 6.5.1):
> ERROR: Illegal use of aggregates or non-group column in HAVING clause
>
>
>
> thanks!!!
>
>
>
>
>
>
> ************
>