Re: Count with Intersect, Union, etc.

Try

SELECT COUNT(*)
FROM CUSTOMERS
WHERE order_number IN
(SELECT order_number FROM CUSTOMERS
 INTERSECT
 SELECT ORDER_NUMBER FROM ORDERS
)

This will work but you can probably do better from a performance viewpoint e.g.

SELECT count(distinct order_number)
FROM customers c,

           orders o
WHERE c.order_number = o.order_number;

On Fri, 27 Mar 1998 10:18:29 -0600, Ryan Rucker <rxruck2_at_uswest.com> wrote:

>Hello,
>
>Is it possible to do a "select count(*)..." kind of query with an
>intersect or union?
>
>Here's what I'm looking for:
>
> select order_number from customers intersect select order_number
>from orders;
>
>While this returns the values I'm looking for, I'd rather just have a
>count. If this is possible, how is it done?
>
>I've tried things like:
>
> select count(order_number) from customers intersect select
>count(order_number)
> from orders;
>
>but I just get "no rows selected."
>
>I've glanced through Oracle: The Complete Reference and didn't see
>anything, and I've also looked through the archives at DejaNews.
>
>Thanks,
>
>Ryan
Received on Sat Mar 28 1998 - 00:00:00 CST