IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

QUERY to get count of distinct values


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Krishnadeva Reddy

New User


Joined: 14 Nov 2007
Posts: 37
Location: Chennai

PostPosted: Thu Nov 10, 2011 2:39 am
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
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: 1020
Location: India

PostPosted: Thu Nov 10, 2011 12:36 pm
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
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: 37
Location: Chennai

PostPosted: Tue Nov 15, 2011 8:12 am
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts INCLUDE OMIT COND for Multiple values... DFSORT/ICETOOL 5
No new posts To get the count of rows for every 1 ... DB2 3
No new posts Replace Multiple Field values to Othe... DFSORT/ICETOOL 12
No new posts To find whether record count are true... DFSORT/ICETOOL 6
No new posts RC query -Time column CA Products 3
Search our Forums:

Back to Top