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

Part of your problem may stem from the fact that you are using two tables in your query but you are not joining them on any column. This leads to a cross product which may not be what you want.

Anyway, try this:

SELECT "a_count" as tabl, COUNT(A.CUSTOMER_NO) as count FROM CUSTOMER A WHERE A.DATE = '01-SEP-99' UNION
SELECT "b_count" as tabl, COUNT(B.ITEMS_PURCHASED) as count FROM SALES B WHERE B.DATE = '01-SEP-99'; This should give output like:

tabl count
----- -----
a_count x
b_count y

If that is not the output you want, use the results with a DECODE like this:

SELECT SUM(DECODE(tabl,'a_count',count,NULL)) as tabl_a,

       SUM(DECODE(tabl,'b_count',count,NULL)) as tabl_b FROM (SELECT "a_count" as tabl, COUNT(A.CUSTOMER_NO) as count FROM CUSTOMER A WHERE A.DATE = '01-SEP-99' UNION
SELECT "b_count" as tabl, COUNT(B.ITEMS_PURCHASED) as count FROM SALES B WHERE B.DATE = '01-SEP-99'); and you should get something like this:

tabl_a tabl_b
------- ------
  x y

HTH,
Brian

stuco_at_mailcity.com 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'
> /
>
> 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?
>
> TIA
>
> Stuart Cowen
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Thu Sep 09 1999 - 15:32:49 CDT