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
notnull
);
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!!!