DB2- COUNT function

geethi_nair · New User Joined: 20 Nov 2007 Posts: 2 Location: US

Hi,

I have Table1 with 22 rows and Table2 with 14 rows. I need to find how many rows of Table1 satisfies the condition Table1.var1 = Table2.var1.

SELECT COUNT(A.VAR1)
FROM Table1 A
,Table2 B
WHERE A.var1 = B.var1

This query returned 308. Please help.

Thanks in advance,

Craq Giegerich · Posted: Sat Mar 01, 2008 6:10 am

Craq Giegerich · Posted: Sat Mar 01, 2008 6:15 am

If all the rows in table1 match all the row in TABLE2 (ie there is only 1 var1 in each table) then 308 is what you should get.

geethi_nair · New User Joined: 20 Nov 2007 Posts: 2 Location: US

Then how is it possible if I want to find the number of rows in Table1 that have a matching Var1 in Table2. (I tried all the joins. But don't work).

The following query did give correct value.

Select *
from Table1
where var1 in (select var1 from Table2)

But I didn't want to use a subquery here. This query seems to be a bit awkward for me because in another occasion I had to write the query as follows:

Select *
from Table1
where var1 in (select var1 from Table2)
and var2 in (select var2 from Table2)

Please help.

dick scherrer · Posted: Tue Mar 04, 2008 7:07 am

Hello,

Please post all of the values of var1 in both tables. Do not show the unique values, but show every row in each table.