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?
Re: [SQL] having with sub select? |
Список | 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 по дате отправления: