counting two fields

Dev Shed Forums Sponsor:

Get inside! Sample the range of functionality easily built with JMSL Library for Time Series Data Analysis, Heat Maps, Portfolio Optimization, Monte Carlo Simulation, Stock Price Charting and more. Download Now!

July 23rd, 2003, 02:42 PM

vb.net

Demonic Swordsman DGQB

Join Date: Apr 2003

Posts: 995

Time spent in forums: 3 Days 12 h 38 m 28 sec
Reputation Power: 76

counting two fields

Hi,

I have two columns: Col_1 has 300 records and Col_2 has 500 records. I want to write a query to display the each count in a field, so 300 in the first field and 500 in the second field. However, the result is 300 x 500 in each field (150000). Here is the SQL. What am I missing?

Code:

select count(Table_1.Col_1), count(Table_2.Col_2)
from Table_1, Table_2

July 23rd, 2003, 03:12 PM

rycamor

Gödelian monster

Join Date: Jul 1999

Location: Pembroke Pines, Florida, USA

Posts: 2,300

Time spent in forums: 3 Days 3 h 2 m 4 sec
Reputation Power: 44

Hi vb.net,

When you do a basic "implied" inner join like this, the query does a cross-mutiply of the results from both columns.

Let me ask, is there any relationship between Col_1 and Col_2 (in other words, do they have a foreign key relationship)?

If so, you could do something like:

Code:

SELECT COUNT(Table_1.Col_1) , COUNT(Table_2.Col_2) 
FROM Table_1
LEFT JOIN Table_2 ON Table_1.Col_1 = Table_2.Col_2

There are other choices, depending on the DBMS you are using, which is...___?

__________________
The real n-tier system:

FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

Amazon wishlist -- rycamor (at) gmail.com

July 23rd, 2003, 04:39 PM

vb.net

Demonic Swordsman DGQB

Join Date: Apr 2003

Posts: 995

Time spent in forums: 3 Days 12 h 38 m 28 sec
Reputation Power: 76

Oracle

July 23rd, 2003, 04:47 PM

rycamor

Gödelian monster

Join Date: Jul 1999

Location: Pembroke Pines, Florida, USA

Posts: 2,300

Time spent in forums: 3 Days 3 h 2 m 4 sec
Reputation Power: 44

OK, given Oracle, or any DBMS with subselects, an easy way to get counts from two totally unrelated tables is simply:

Code:

select (select count(Col_1) from Table_1) AS Count_1, (select count(Col_2) from Table_2) AS Count_2;

July 23rd, 2003, 06:51 PM

r937

SQL Consultant

Dev Shed God 25th Plane (17000 - 17499 posts)

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,343

Time spent in forums: 1 Month 3 Weeks 5 Days 8 h 5 m 22 sec
Reputation Power: 891

a variation:

Code:

  select 'Table 1:', count(Col_1) from Table_1
union all
  select 'Table 2:', count(Col_1) from Table_2

rudy

July 24th, 2003, 09:40 AM

vb.net

Demonic Swordsman DGQB

Join Date: Apr 2003

Posts: 995

Time spent in forums: 3 Days 12 h 38 m 28 sec
Reputation Power: 76

thank you both. I figured out the SQL to do exactly what I want:

Code:

select distinct (select count(Col_1) from Table_1) AS 1, (select count(Col_2) from Table_2) AS 2
from Table_1, Table_2;

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: