How do I get a Count(*) for column having nulls
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

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

  1. #1
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    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


  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    select count(distinct colA), count(1)
    from Test group by colA;


  3. #3
    Join Date
    Apr 2001
    Posts
    47
    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,

  4. #4
    Join Date
    Oct 2000
    Posts
    467
    SELECT COLUMN, COUNT(*) FROM TABLE
    GROUP BY COLUMN;

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


    CHEERS
    Vinit

  5. #5
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    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

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    SELECT COUNT(*) FROM
    (SELECT DISTINCT colA FROM test);
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width