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" <[EMAIL PROTECTED]> wrote: > On Thu, Mar 27, 2008 at 10:16 AM, Olaf Stein > <[EMAIL PROTECTED]> 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.
