How to find the NULL counts and non NULL counts?

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_nullcount_of_not_nullcount(*)
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> wrote in message
news:1162305541.838434.188080@.f16g2000cwb.googlegr oups.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.
|||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_nullcount_of_not_nullcount(*)
>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!
|||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.googlegr oups.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.
sql

How to find the NULL counts and non NULL counts?

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> 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.|||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!|||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.

How to find the NULL counts and non NULL counts?

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> 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.|||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!|||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...
> > 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.