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!!!
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? |