Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

DB2- COUNT function

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
geethi_nair

New User


Joined: 20 Nov 2007
Posts: 2
Location: US

PostPosted: Sat Mar 01, 2008 5:18 am    Post subject: DB2- COUNT function
Reply with quote

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,
Back to top
View user's profile Send private message

Craq Giegerich

Senior Member


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

PostPosted: Sat Mar 01, 2008 6:10 am    Post subject: Re: DB2- COUNT function
Reply with quote

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?
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


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

PostPosted: Sat Mar 01, 2008 6:15 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
geethi_nair

New User


Joined: 20 Nov 2007
Posts: 2
Location: US

PostPosted: Tue Mar 04, 2008 5:03 am    Post subject: Reply to: DB2- COUNT function
Reply with quote

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.
Back to top
View user's profile Send private message
dick scherrer

Site Director


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

PostPosted: Tue Mar 04, 2008 7:07 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Count the length of the record & ... sreekusr DFSORT/ICETOOL 4 Thu Mar 23, 2017 7:52 pm
No new posts Display FTP Session Count within TSO Yolanda Harvey TSO/ISPF 3 Fri Mar 10, 2017 10:31 pm
No new posts Count Trailing Spaces in variable str... Virendra Shambharkar SYNCSORT 10 Thu Feb 02, 2017 12:23 pm
This topic is locked: you cannot edit posts or make replies. RANDOM Function in COBOL swapnil781 COBOL Programming 2 Tue Nov 15, 2016 6:17 pm
No new posts Check System time(Minute) Using TIME1... balaji81_k DFSORT/ICETOOL 5 Fri Nov 11, 2016 10:53 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us