having with sub select?

Поиск
Список
Период
Сортировка
От
Тема having with sub select?
Дата
Msg-id Pine.LNX.4.10.9912151727010.7112-100000@picasso.realtyideas.com
обсуждение исходный текст
Ответ на Transactions and functions  ("<Miguel Sofer" <mig@utdt.edu>)
Ответы Re: [SQL] having with sub select?  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: [SQL] having with sub select?  (<kaiq@realtyideas.com>)
Список pgsql-sql
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!!!







В списке pgsql-sql по дате отправления:

Предыдущее
От: "brent wood"
Дата:
Сообщение: Re: [GENERAL] Access rescrictions
Следующее
От: Tom Lane
Дата:
Сообщение: Re: [SQL] having with sub select?