View previous topic :: View next topic
|
Author |
Message |
Krishnadeva Reddy
New User
Joined: 14 Nov 2007 Posts: 37 Location: Chennai
|
|
|
|
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 |
|
|
saiprasadh
Active User
Joined: 20 Sep 2006 Posts: 154 Location: US
|
|
|
|
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 |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
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 |
|
|
Krishnadeva Reddy
New User
Joined: 14 Nov 2007 Posts: 37 Location: Chennai
|
|
|
|
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 |
|
|
|