Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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 column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm
No new posts Updating the Trailer count in variabl... satheshbabur DFSORT/ICETOOL 6 Wed Aug 30, 2017 9:49 pm
No new posts SORT Trailer Count - LRECL Output co... amorante DFSORT/ICETOOL 5 Tue Aug 29, 2017 8:57 pm
No new posts CICS Transaction attach count of supp... lind sh CICS 1 Wed Jun 21, 2017 1:33 pm
No new posts What is the Extract Function in JCL dharmaraok JCL & VSAM 3 Wed Jun 14, 2017 9:12 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us