Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Posts
    39

    Unanswered: counting nulls in a query

    Very basic question?
    How to count the rows in a table where column != 'SOME VALUE'
    Here is the situation: The apps table have 21 rows.
    5 rows with category = 'CORE' and rest category is null.

    SQL> select count(*) from apps ;
    COUNT(*)
    ----------
    21
    SQL> select count(*) from apps where category ='CORE' ;
    COUNT(*)
    ----------
    5
    SQL> select count(*) from apps where category != 'CORE' ;
    COUNT(*)
    ----------
    0 ... why the answer is not 16

    Rest of the values in the table are nulls. This is suppose to be
    a simple query. Why the result is not 16 ( 21 -5). Is there any
    different way to treat nulls ?
    I used following tow work around to get correct answer.... but is there any standard way to treat these nulls.
    1 select count(*) from apps where category != 'CORE'
    or category is NULL ;
    OR
    2 select count(*) from apps where 'ABCDEFG' =
    nvl2(category,category,'ABCDEFG') ;

    COUNT(*)
    ----------
    16

    Thanks

  2. #2
    Join Date
    Jan 2004
    Posts
    370
    See the following link for an explanation:

    http://download-west.oracle.com/docs...ts5a.htm#59163

  3. #3
    Join Date
    Feb 2004
    Posts
    143
    I was just curious -
    Why don't you use
    select count(*) from apps where category IS NULL;

    to get the count of rows where category is null rather than going the other way around which really doesn't make any sense

  4. #4
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482
    In Oracle rows with NULL values are not threated as blank or zero value rows. They are "unknown" to Oracle since NULL cannot be quantified. That's why the != 'CORE' doesn't count the NULL rows.

    HTH,

    clio_usa - OCP 8/8i/9i DBA

Posting Permissions

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