MS SQL Server - How to find the NULL counts and non NULL counts?

Author

How to find the NULL counts and non NULL counts?

dba_222@yahoo.com

2006-10-31, 7:12 pm

Dear experts,

I am finding a LOT of rows with NULL columns in the Sybase
tables I'm querying.

Say, there is a table, with 100 rows.
25 rows are NULL
75 rows are NOT NULL.

What I'm trying to eliminate is:

select count(*)
from some_table
where fieldx is null

and then running the next query:

select count(*)
from some_table
where fieldx is NOT null

What functions can I use to run a query such as:

select count(f1( fieldx ),) AS count_of_null,
count(f2( fieldx ) ) AS count_of_not_null,
count(*)
from some_table

that would return one row that would look like:

count_of_null count_
of_not_null count(*)

25 75 100

I know there is the ISNULL function. But that converts the NULL
to an actual number. Could I use other functions in conjunction with
it?

Thanks a lot!

Mike C#

2006-10-31, 7:12 pm

<dba_222@yahoo.com> wrote in message
news:1162305541.838434.188080@f16g2000cwb.googlegroups.com...
> Dear experts,
>
> I am finding a LOT of rows with NULL columns in the Sybase
> tables I'm querying.
>
> Say, there is a table, with 100 rows.
> 25 rows are NULL
> 75 rows are NOT NULL.
>
>
> What I'm trying to eliminate is:
>
> select count(*)
> from some_table
> where fieldx is null
>
> and then running the next query:
>
> select count(*)
> from some_table
> where fieldx is NOT null
>
>
> What functions can I use to run a query such as:
>
> select count(f1( fieldx ),) AS count_of_null,
> count(f2( fieldx ) ) AS count_of_not_null,
> count(*)
> from some_table
>
>
> that would return one row that would look like:
>
>
> count_of_null count_of_not_null count(*)
>
> 25 75 100
>
>
>
> I know there is the ISNULL function. But that converts the NULL
> to an actual number. Could I use other functions in conjunction with
> it?

SELECT COUNT(fieldx) AS count_of_not_null,
COUNT(*) - COUNT(fieldx) AS count_of_null
FROM some_table

Is how you would do it with MS SQL Server. Should also work with Sybase,
but I don't have a Sybase server to test on.

Roy Harvey

2006-10-31, 7:12 pm

SELECT COUNT(*) as TotalRows,
COUNT(Col1) as Col1_NotNull,
COUNT(Col2) as Col2_NotNull,
COUNT(Col3) as Col3_NotNull
FROM TableWithNulls

The first column tells you the total number of rows in the table, the
other columns the number of non-nulls for the column specified. Not
that you can deal with all the columns in one SELECT.

Roy Harvey
Beacon Falls, CT

On 31 Oct 2006 06:39:01 -0800, dba_222@yahoo.com wrote:

>Dear experts,
>
>I am finding a LOT of rows with NULL columns in the Sybase
>tables I'm querying.
>
>Say, there is a table, with 100 rows.
>25 rows are NULL
>75 rows are NOT NULL.
>
>
>What I'm trying to eliminate is:
>
>select count(*)
>from some_table
>where fieldx is null
>
>and then running the next query:
>
>select count(*)
>from some_table
>where fieldx is NOT null
>
>
>What functions can I use to run a query such as:
>
>select count(f1( fieldx ),) AS count_of_null,
> count(f2( fieldx ) ) AS count_of_not_null,
> count(*)
>from some_table
>
>
>that would return one row that would look like:
>
>
> count_of_null count_
of_not_null count(*)

>
>25 75 100
>
>
>
>I know there is the ISNULL function. But that converts the NULL
>to an actual number. Could I use other functions in conjunction with
>it?
>
>
>
>Thanks a lot!

dba_222@yahoo.com

2006-10-31, 7:12 pm

Brilliant!

I really should have thought of that.
But it was a looong tedious day yesterday.

Thanks a lot!

Mike C# wrote:
> <dba_222@yahoo.com> wrote in message
> news:1162305541.838434.188080@f16g2000cwb.googlegroups.com...
>
> SELECT COUNT(fieldx) AS count_of_not_null,
> COUNT(*) - COUNT(fieldx) AS count_of_null
> FROM some_table
>
> Is how you would do it with MS SQL Server. Should also work with Sybase,
> but I don't have a Sybase server to test on.

Sponsored Links