pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Re: [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