pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: [SQL] having with sub select?
4+ messages / 2 participants
[nested] [flat]
* having with sub select?
@ 1999-12-15 23:47 kaiq@realtyideas.com
1999-12-15 23:33 ` Re: [SQL] having with sub select? Tom Lane <tgl@sss.pgh.pa.us>
1999-12-16 00:30 ` Re: [SQL] having with sub select? kaiq@realtyideas.com
0 siblings, 2 replies; 4+ messages in thread
From: kaiq@realtyideas.com @ 1999-12-15 23:47 UTC (permalink / raw)
To: 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!!!
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] having with sub select?
1999-12-15 23:47 having with sub select? kaiq@realtyideas.com
@ 1999-12-15 23:33 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 1999-12-15 23:33 UTC (permalink / raw)
To: kaiq@realtyideas.com; +Cc: pgsql-sql
<kaiq@realtyideas.com> writes:
> select rep, sum(amount)
> from orders
> group by rep
> having sum(amount) < (select quota from salesreps where empl_num = rep);
> for the query, I got (I'm using 6.5.1):
> ERROR: Illegal use of aggregates or non-group column in HAVING clause
Seems to work OK in 6.5.3 and in current development sources ... I'm not
quite sure why it fails in 6.5.1 and not in 6.5.3, because I can't find
any indication that parse_aggs.c changed in between. But please update
and see if you still have the problem.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] having with sub select?
1999-12-15 23:47 having with sub select? kaiq@realtyideas.com
@ 1999-12-16 00:30 ` kaiq@realtyideas.com
1999-12-16 03:00 ` Re: [SQL] having with sub select? Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 4+ messages in thread
From: kaiq@realtyideas.com @ 1999-12-16 00:30 UTC (permalink / raw)
To: pgsql-sql
sorry, I messed up. Actually the query works. but how can I put
the quota within the select? is it possible?
select rep, sum(amount), quota
from orders, salesreps
where empl_num = rep
group by rep
having sum(amount) < (select quota from salesreps where empl_num = rep);
the error:
ERROR: Illegal use of aggregates or non-group column in target list
I know that for one table, with "group", select can only have the grouping
non-aggregate attributes, and it makes sense. However, for two tables,
the constraint does not make sense--i.e., it is required in many
situations. Here, every sales-representative has and only has one
quota. so, it is quite reasonable to select both.
Is there any work arounds except making two queries?
thanks
On Wed, 15 Dec 1999 kaiq@realtyideas.com wrote:
> 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!!!
>
>
>
>
>
>
> ************
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] having with sub select?
1999-12-15 23:47 having with sub select? kaiq@realtyideas.com
1999-12-16 00:30 ` Re: [SQL] having with sub select? kaiq@realtyideas.com
@ 1999-12-16 03:00 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 1999-12-16 03:00 UTC (permalink / raw)
To: kaiq@realtyideas.com; +Cc: pgsql-sql
<kaiq@realtyideas.com> writes:
> select rep, sum(amount), quota
> from orders, salesreps
> where empl_num = rep
> group by rep
> having sum(amount) < (select quota from salesreps where empl_num = rep);
> ERROR: Illegal use of aggregates or non-group column in target list
> I know that for one table, with "group", select can only have the grouping
> non-aggregate attributes, and it makes sense. However, for two tables,
> the constraint does not make sense--i.e., it is required in many
> situations. Here, every sales-representative has and only has one
> quota. so, it is quite reasonable to select both.
Well, the brute-force way is to group by both columns, which also lets
you get rid of the subselect:
select rep, sum(amount), quota
from orders, salesreps
where empl_num = rep
group by rep, quota
having sum(amount) < quota;
which will give you only one output row per rep if the reps do only have
one quota. Another way, which depends even more on the assumption that
there's only one quota value per rep, is to use a "dummy" aggregate:
select rep, sum(amount), min(quota)
from orders, salesreps
where empl_num = rep
group by rep
having sum(amount) < min(quota);
(max(quota) would work just as well, of course, or even sum or avg).
But both of these feel like unsatisfying kluges. Any SQL experts know
the conceptually "right" way to do this?
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~1999-12-16 03:00 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-12-15 23:47 having with sub select? kaiq@realtyideas.com
1999-12-15 23:33 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-12-16 00:30 ` kaiq@realtyideas.com
1999-12-16 03:00 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox