Percentage Question Total Records Issue

[earljgray2]

Good Day...

I have the task of getting a percentage of NULL entries in a table ion a particular field. The method I WAS going to use was count(*) to derive the count of all records where the given field is either NULL or NOT NULL and Sum(CASE WHEN [field] IS NULL then 1 ELSE 0 END) then simply take the percentage.

The query returns the correct number of NULL records. The COUNT(*) is incorrect, however, since there may be multiple records for one 'owner':

Owner A has 2 non-null records
Owner B has 1 non-null records
Owner C has 3 non-null records

The TOTAL for the table should be 6 non-null + 22 NULL. I need the total number of OWNERS, not RECORDS.

Anyway to do this in a single query?

 

[djj55]

If I understand you might try COUNT(DISTINCT Owner)

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[earljgray]

Thanks... as it turns out, a more convoluted query was required to capture another aspect of the table. I appreciate your response!

Crystal Reports Design/training/Consultation
earljgray@gmail.com