Re: Count with Intersect, Union, etc.

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/03/27
Message-ID: <351fd575.11698621@192.86.155.100>#1/1

A copy of this was sent to Ryan Rucker <rxruck2_at_uswest.com> (if that email address didn't require changing) On Fri, 27 Mar 1998 10:18:29 -0600, you 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."
>

Try inline views. For example:

SQL> connect scott/tiger
Connected.

SQL> select count(*)
  2 from ( select deptno from emp

  3           intersect
  4           select deptno from dept );

  COUNT(*)

---

         3

SQL> select count(*)
  2 from ( select deptno from emp

  3           union all
  4           select deptno from dept );

  COUNT(*)

---

        18

with inline views you can "select from a select"...

>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
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Mar 27 1998 - 00:00:00 CST