How do I get a Count(*) for column having nulls

[ronnie]

Hi,
I have a table which on which I want to get the total number of distinct vales for a particular column.

Fore Example
Table = Test
Column = ColA

the values is column ColA are

23
23
44
44
44
44
11
11
11
null
null

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.

Please Suggest

Ronnie

[tamilselvan]

select count(distinct colA), count(1)
from Test group by colA;

[dmitriy]

Hi,

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.
Your phrase
...will give me a count of all the distinct values in the column, in this case 4
seems to me a very controversial issue.

Regards,

[vinit]

SELECT COLUMN, COUNT(*) FROM TABLE
GROUP BY COLUMN;

tHIS SHOULD GIVE YOU THE RESULT YOU'RE LOOKNG FOR.


CHEERS

[ronnie]

Hi,
Thanks for the replies. dmitriy response helped me solve the problem. Is there any way I can do it without using the nvl function

Thanks
Ronnie

[jmodic]

SELECT COUNT(*) FROM
(SELECT DISTINCT colA FROM test);