Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref

Author Message
geethi_nair

New User

Joined: 20 Nov 2007
Posts: 2
Location: US

 Posted: Sat Mar 01, 2008 5:18 am    Post subject: DB2- COUNT function 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

Senior Member

Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

Posted: Sat Mar 01, 2008 6:10 am    Post subject: Re: DB2- COUNT function

 geethi_nair wrote: 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,

What is the problem?
Craq Giegerich

Senior Member

Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

 Posted: Sat Mar 01, 2008 6:15 am    Post subject: 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

 Posted: Tue Mar 04, 2008 5:03 am    Post subject: Reply to: DB2- COUNT function 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

Site Director

Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

 Posted: Tue Mar 04, 2008 7:07 am    Post subject: 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.
 All times are GMT + 6 Hours
 Page 1 of 1

Search our Forum:

 Topic Author Forum Replies Posted Similar Topics ISMF Difference between volume count ... upendrasri IBM Tools 2 Tue Dec 05, 2017 12:40 pm How to write Rexx program to size and... sreejeshcs CLIST & REXX 14 Thu Oct 12, 2017 7:26 am column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm Updating the Trailer count in variabl... satheshbabur DFSORT/ICETOOL 6 Wed Aug 30, 2017 9:49 pm SORT Trailer Count - LRECL Output co... amorante DFSORT/ICETOOL 5 Tue Aug 29, 2017 8:57 pm

 © 2003-2017 IBM MAINFRAME Software Support Division
 Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us