Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: How do I count Columns in Different Tables??
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