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
 

 

QUERY to get count of distinct values

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

New User


Joined: 14 Nov 2007
Posts: 36
Location: Chennai

PostPosted: Thu Nov 10, 2011 2:39 am    Post subject: QUERY to get count of distinct values
Reply with quote

Hi,

I have written a query to get count of distinct values on 3 columns, but the blank value also considered and included in the count.

Could you please help me to get rid of blank values in the count.The data type on the column is CHAR.

This is the query I have executed:

Code:

SELECT P#, COUNT(DISTINCT(COL1)),
COUNT(DISTINCT(COL2)),
COUNT(DISTINCT(COL3))
FROM TABLE_123
WHERE P_SRC = 'M'
  AND DTM > CURRENT DATE - 1 YEAR
 GROUP BY P#;


This is the output from the query


P# COUNT1 COUNT2 COUNT3
1145575 2 1 1
2145234 2 1 1
3005236 1 2 1

The table has below data

P# COL1 COL2 COL3
1145575 100.98
1145575 100.98
1145575 100.99 100.99
1145575 100.99
2145234 100.97 100.97
2145234 100.97 100.97
2145234 100.99
2145234 100.99 100.99
3005236 99.99 97.97
3005236 97.97
3005236 97.98

Expected output is:

P# COUNT1 COUNT2 COUNT3
1145575 2 1 0
2145234 2 0 1
3005236 1 2 0
Back to top
View user's profile Send private message

saiprasadh

Active User


Joined: 20 Sep 2006
Posts: 154
Location: US

PostPosted: Thu Nov 10, 2011 4:15 am    Post subject:
Reply with quote

Hi Krishna,

I'm getting correct output . Please show your table definition.



Output:

Code:
1145575   2   1   0
2145234   2   2   0
3005236   3   1   0
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Thu Nov 10, 2011 12:36 pm    Post subject:
Reply with quote

Hey Krishna,

In the INPUT data you gave there is no value for COL3 in P#(2145234)

And try to use CODE tag

Below SQL Not Tested
Code:
SELECT P#, COUNT(DISTINCT(nullif(col1,' '))),
COUNT(DISTINCT(nullif(col2,' '))),
COUNT(DISTINCT(nullif(col3,' ')))
FROM TABLE_123
WHERE P_SRC = 'M'
  AND DTM > CURRENT DATE - 1 YEAR
 GROUP BY P#;

Thanks,
Sushanth
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Thu Nov 10, 2011 2:37 pm    Post subject:
Reply with quote

DB2 v9
these are 2 ways , both involving subselects
each with their own performance depending on number of rows and cardinalities. In most cases solution 2 should be faster
Code:
select p
,  (select count(distinct(a1.col1)) from A a1 where a1.p = a0.p and a1.col1 <> '      ')
,  (select count(distinct(a2.col2)) from A a2 where a2.p = a0.p and a2.col2 <> '      ')
,  (select count(distinct(a3.col3)) from A a3 where a3.p = a0.p and a3.col3 <> '      ')
from a a0
group by P

or
Code:
select p
, count(distinct(a0.col1)) - coalesce((select 1 from A a1 where a1.p = a0.p and a1.col1 = '      ' fetch first row only),0)
, count(distinct(a0.col2)) - coalesce((select 1 from A a1 where a1.p = a0.p and a1.col2 = '      ' fetch first row only),0)
, count(distinct(a0.col3)) - coalesce((select 1 from A a1 where a1.p = a0.p and a1.col3 = '      ' fetch first row only),0)
from a a0
group by P
Back to top
View user's profile Send private message
Krishnadeva Reddy

New User


Joined: 14 Nov 2007
Posts: 36
Location: Chennai

PostPosted: Tue Nov 15, 2011 8:12 am    Post subject:
Reply with quote

Thanks everyone for your help.

I got the results from the below query:

Code:

SELECT P#, COUNT(DISTINCT(nullif(col1,' '))),
COUNT(DISTINCT(nullif(col2,' '))),
COUNT(DISTINCT(nullif(col3,' ')))
FROM TABLE_123
WHERE P_SRC = 'M'
  AND DTM > CURRENT DATE - 1 YEAR
 GROUP BY P#;
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 Sorting of hex values Saurabh_mi DFSORT/ICETOOL 11 Thu May 25, 2017 3:49 pm
No new posts Replace values in the input data Vikas Maharnawar DFSORT/ICETOOL 10 Thu May 11, 2017 2:18 pm
No new posts Check if any Detail records and extra... V S Amarendra Reddy SYNCSORT 19 Mon May 08, 2017 8:54 pm
This topic is locked: you cannot edit posts or make replies. Merge two files and update Trailer re... Yashashri JCL & VSAM 1 Thu May 04, 2017 12:54 pm
No new posts Compare two files and subtract values ameetmund DFSORT/ICETOOL 7 Fri Mar 31, 2017 3:35 pm


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