I have a table which on which I want to get the total number of distinct vales for a particular column.
Table = Test
Column = ColA
the values is column ColA are
Note that the null fields are actually blanks and not populated with "null"
Now I want to do a select query which will give me a count of all the distinct values in the column, in this case 4
I run a query like this
select count(distinct colA) from Test;
and the reult is 3, it ignores the nulls.
How do I get them also.
select count(distinct colA), count(1)
from Test group by colA;
It would be
select count(distinct nvl(ColA, 'null')) from test
group by ColA
if you are ready to accept all nulls like one distinct value.
...will give me a count of all the distinct values in the column, in this case 4
seems to me a very controversial issue.
SELECT COLUMN, COUNT(*) FROM TABLE
GROUP BY COLUMN;
tHIS SHOULD GIVE YOU THE RESULT YOU'RE LOOKNG FOR.
Thanks for the replies. dmitriy response helped me solve the problem. Is there any way I can do it without using the nvl function
SELECT COUNT(*) FROM
(SELECT DISTINCT colA FROM test);
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Click Here to Expand Forum to Full Width