Olaf Stein <[EMAIL PROTECTED]> 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
