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
 

 

Get counts of all columns having value > space

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

New User


Joined: 19 Jan 2006
Posts: 17
Location: bangalore

PostPosted: Fri Jan 16, 2009 12:13 pm    Post subject: Get counts of all columns having value > space
Reply with quote

hi,

i need to get counts of all columns in a table having value > space.
can i get it using single query?if yes,please let me know the query syntax.

thanks
rups
Back to top
View user's profile Send private message

guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Fri Jan 16, 2009 1:19 pm    Post subject:
Reply with quote

Hellp Rups,

Your query is not very clear.
Do you want to find number of rows having value greater than space for particular column?
Back to top
View user's profile Send private message
rups

New User


Joined: 19 Jan 2006
Posts: 17
Location: bangalore

PostPosted: Fri Jan 16, 2009 2:13 pm    Post subject: Reply to: Get counts of all columns having value > space
Reply with quote

hello guptae,

yes i want to find total no of rows having value greater than space of all the column present in a table.


suppose if i have a table just like below

emp-id name
1 xx
2
3 yy


Query should give me answer following answer

emp-id = 3(since all the three rows has data)
name=2(only two rows has data)

i want to get the above result by executing single query.






thanks
rups
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Fri Jan 16, 2009 2:25 pm    Post subject:
Reply with quote

Try

Code:


SELECT COUNT(EMPID),COUNT(NAME) FROM TABLE WHERE EMPID > '' OR NAME > '' ;



not tested ...
Back to top
View user's profile Send private message
rups

New User


Joined: 19 Jan 2006
Posts: 17
Location: bangalore

PostPosted: Fri Jan 16, 2009 2:48 pm    Post subject: Reply to: Get counts of all columns having value > space
Reply with quote

hi ashimer,

i tried your query,but i got the answer of emp-id count as 3 and name as 3.

thanks
rups
Back to top
View user's profile Send private message
Bharath Bhat

Active User


Joined: 20 Mar 2008
Posts: 283
Location: chennai

PostPosted: Fri Jan 16, 2009 3:26 pm    Post subject:
Reply with quote

Try
Code:

SELECT COUNT(empid)
      ,(SELECT COUNT(name)
          FROM table1
         WHERE name > ' '
        )
  FROM table1
 WHERE empid > ' '
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Fri Jan 16, 2009 3:33 pm    Post subject:
Reply with quote

Hello,
Quote:
tried your query,but i got the answer of emp-id count as 3 and name as 3.
And what did you expect ? Your table definition is not known to us so no one (me) can not comment much on your this post.
Back to top
View user's profile Send private message
rups

New User


Joined: 19 Jan 2006
Posts: 17
Location: bangalore

PostPosted: Fri Jan 16, 2009 3:56 pm    Post subject: Reply to: Get counts of all columns having value > space
Reply with quote

hello anuj,

here is the structure of the table

Emp-id Name
1 xx
2
3 yy


emp-id is defined as char(3) NOT NULL
name as char(10) NOT NULL
Back to top
View user's profile Send private message
murugan_mf

Active User


Joined: 31 Jan 2008
Posts: 148
Location: Chennai, India

PostPosted: Fri Jan 16, 2009 5:12 pm    Post subject:
Reply with quote

Code:
SELECT 'EMPID', COUNT(EMP-ID)                       
FROM EMPTAB WHERE EMP-ID<>SPACE(3)   
UNION                                           
SELECT 'EMPNAME',COUNT(NAME)                       
FROM EMPTAB WHERE NAME<>SPACE(10);
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
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Comparing Decimal and CHAR columns rakesh17684 DB2 7 Thu Oct 20, 2016 2:33 am
No new posts Difference space showed for TS and it... autobox DB2 1 Thu Apr 14, 2016 1:07 pm
No new posts Combine columns without breaking words Indrajit_57 SYNCSORT 1 Sat Mar 19, 2016 9:33 am
No new posts How to find Table-space and Index-spa... venksiv DB2 5 Wed Feb 03, 2016 6:16 pm


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