On 4/8/07, Igor Tandetnik <[EMAIL PROTECTED]> wrote:
Jaime Castells <[EMAIL PROTECTED]> wrote:
> Hmm, I think the only time you really can't avoid a nested query is
> when you
> want to do a grouping function on the result of a grouping function.
> Like
> count the number of customers who have more than a certain number of
> orders.
> Something like:
>
> SELECT COUNT(cust.id)
> FROM (SELECT cust.id, COUNT(order.id) AS order_count FROM cust, order
> WHERE order.cust_id=cust.id GROUP BY cust.id)
> WHERE order_count>10
> ;
select count(cust.id) from cust
where (
select count(*) from order
where order.cust_id = cust.id) > 10;
I think you could also do that with this:
select cust.id, count(*)
from order
inner join order on order.cust_id = cust.id
group by cust.id
having count(*) > 10
