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: 1278
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 Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
This topic is locked: you cannot edit posts or make replies. MQ setup query mohitsaini Java & MQSeries 4 Sun Jan 08, 2017 1:53 am
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
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


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