Hey all,

I have a table with 40 columns. If for a record a value is not available the
column is set to NULL. Is there a quick way of finding out how many records
have a value (NOT NULL) for 90% (or lets say 35 columns) of the columns.

Thanks
Olaf

----------------------------------------- Confidentiality Notice:
The following mail message, including any attachments, is for the
sole use of the intended recipient(s) and may contain confidential
and privileged information. The recipient is responsible to
maintain the confidentiality of this information and to use the
information only for authorized purposes. If you are not the
intended recipient (or authorized to receive information for the
intended recipient), you are hereby notified that any review, use,
disclosure, distribution, copying, printing, or action taken in
reliance on the contents of this e-mail is strictly prohibited. If
you have received this communication in error, please notify us
immediately by reply e-mail and destroy all copies of the original
message. Thank you.

On Thu, Mar 27, 2008 at 10:16 AM, Olaf Stein <
olaf.stein@nationwidechildrens.org> wrote:

> I have a table with 40 columns. If for a record a value is not available
> the
> column is set to NULL. Is there a quick way of finding out how many
> records
> have a value (NOT NULL) for 90% (or lets say 35 columns) of the columns.
>

Quick and dirty I would write a query that would compute a score for each
row based on the number of rows:
if(col1 IS NULL,0,1)+if(col2 IS NULL,0,1)

and based on that perform a test to see if the row is NULL'enough (or
whatever):
if(if(col1 IS NULL,0,1)+if(col2 IS NULL,0,1) +if(col2 IS NULL,0,1) +if(col2
IS NULL,0,1)>3, 1,0)
so that will return 1 if 3 out of 4 are NULL, and 0 otherwise.

and you could throw all of that inside of a sum() and get the number of rows
that fit your pattern.

--
Rob Wultsch

Thanks.
But that means I have to type all 40 column names, which I what IU was
trying to avoid


Olaf

On 3/27/08 1:34 PM, "Rob Wultsch" <wultsch@gmail.com> wrote:

> On Thu, Mar 27, 2008 at 10:16 AM, Olaf Stein
> <olaf.stein@nationwidechildrens.org> wrote:
>> I have a table with 40 columns. If for a record a value is not available the
>> column is set to NULL. Is there a quick way of finding out how many records
>> have a value (NOT NULL) for 90% (or lets say 35 columns) of the columns.
>
> Quick and dirty I would write a query that would compute a score for each row
> based on the number of rows:
> if(col1 IS NULL,0,1)+if(col2 IS NULL,0,1)
>
> and based on that perform a test to see if the row is NULL'enough (or
> whatever):
> if(if(col1 IS NULL,0,1)+if(col2 IS NULL,0,1) +if(col2 IS NULL,0,1) +if(col2 IS
> NULL,0,1)>3, 1,0)
> so that will return 1 if 3 out of 4 are NULL, and 0 otherwise.
>
> and you could throw all of that inside of a sum() and get the number of rows
> that fit your pattern.




----------------------------------------- Confidentiality Notice:
The following mail message, including any attachments, is for the
sole use of the intended recipient(s) and may contain confidential
and privileged information. The recipient is responsible to
maintain the confidentiality of this information and to use the
information only for authorized purposes. If you are not the
intended recipient (or authorized to receive information for the
intended recipient), you are hereby notified that any review, use,
disclosure, distribution, copying, printing, or action taken in
reliance on the contents of this e-mail is strictly prohibited. If
you have received this communication in error, please notify us
immediately by reply e-mail and destroy all copies of the original
message. Thank you.

Olaf Stein <olaf.stein@nationwidechildrens.org> wrote on 03/27/2008
01:16:43 PM:

> Hey all,
>
> I have a table with 40 columns. If for a record a value is not available
the
> column is set to NULL. Is there a quick way of finding out how many
records
> have a value (NOT NULL) for 90% (or lets say 35 columns) of the columns.
>
> Thanks
> Olaf
>
Try something like:

Select
sum(case when column1 is not null then 1 else 0 end) as
column1NotNullCount,
sum(case when column2 is not null then 1 else 0 end) as
column2NotNullCount,
....
from table

You can use the concat function to create the individual column statements
so you don't have to type 35 selects items:

select concat('sum(case when ', column_name, ' is not null then 1 else 0
end) as ', column_name, 'NotNullCount,')
from information_schema.columns
where table_schema='YourDBNameHere'
and table_name= 'YourTableNameHere'




Donna