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
 
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 How to write Rexx program to size and... sreejeshcs CLIST & REXX 14 Thu Oct 12, 2017 7:26 am
No new posts Extend the decimal values in DIVISION Balaryan DFSORT/ICETOOL 3 Thu Oct 05, 2017 4:05 pm
No new posts HEX value search in a DB2 query maxsubrat DB2 2 Wed Oct 04, 2017 3:04 pm
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm

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