having with sub select?

From: <kaiq(at)realtyideas(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: having with sub select?
Date: 1999-12-15 23:47:31
Message-ID: Pine.LNX.4.10.9912151727010.7112-100000@picasso.realtyideas.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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 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!!!

In response to

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message kaiq 1999-12-16 00:30:06 Re: [SQL] having with sub select?
Previous Message Tom Lane 1999-12-15 23:33:55 Re: [SQL] having with sub select?