Re: [SQL] having with sub select?

<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