Jay Sprenkle <[EMAIL PROTECTED]> wrote:
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
This returns a list of customers that have more than 10 orders. Jaime
wanted a count of such customers, not a list of them. His original query
produces a single number.
Igor Tandetnik
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
