Skip to Content
Former Member
Jul 12, 2008 at 12:49 AM

Distinct Count of Non-null Values


I have a table that has one column for providerID and then a providerID in each of several columns if the provider is under a particular type of contract.

I need a distict count of each provider under each type of contract for every county in the US.

distinct count is almost always one more than the actual distict count because most counties have at least one provider that does not have a particular contract and the distict count counts the null value as a distict value.

I know I can alter the fields to have a zero for nulls, ask for a minimum count and then subtract 1 from the distict count if the minimum is zero, but I hope there is an easier way to figure distict counts of non-null values.

any suggestions?