Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How do I count Columns in Different Tables??

Re: How do I count Columns in Different Tables??

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 09 Sep 1999 16:39:23 -0400
Message-ID: <bhrYN7TuBNXvkiRK8qf9FZYAaQ4Y@4ax.com>


A copy of this was sent to stuco_at_mailcity.com (if that email address didn't require changing) On Thu, 09 Sep 1999 20:00:54 GMT, you wrote:

>Problem:
>
>I need to perform an aggregate count of columns in different tables
>that have relationships to each other. Here's how I envision it, but it
>is not working:
>
> SELECT COUNT(A.CUSTOMER_NO), COUNT(B.ITEMS_PURCHASED)
> FROM CUSTOMER A, SALES B
> WHERE A.DATE = '01-SEP-99'
> AND B.DATE = '01-SEP-99'
> /
>

select * from
( select count(customer_no) cust_cnt from customer where date = '01-SEP-99' ), ( select count(items_purchased) items_cnt from sales where date = '01-SEP-99' ) /

>What I get is the same number on each count() every time, but in
>separate queries the number is different. Is there anyway to accomplish
>this with one query?
>

your query did a cartesian product of A with B -- the counts you got were the count of a TIME the count of b.

the second query gets the 2 individual counts and cartesian products them together -- giving you 1 row with the individual counts.

>TIA
>
>Stuart Cowen
>
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Sep 09 1999 - 15:39:23 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US