>>>>>select count( case when mytable.myfield = 0 then 0 else distinct( mytable.myfield ) end ) as TotCount >>>>>>>>>>
select count( distinct mytable.myfield ) as TotCount from myTable where mytable.myfield <> 0>>>
>Sample table > >FIELD1 FIELD2 >ABC 1 >CBS 2 >ABC 1 >NBC 3 >NBC 2 >CBS 0 >ABC 1 >NBC 0 >NBC 0 >CBS 2 >CBS 0 >CBS 3 >ABC 0 >XYZ 0 >NBC 5 >NBC 5 >XYZ 0 > >Report Explanation >ABC 1 There is only 1 value of ABC (other than 0) >CBS 2 There are 2 different values of CBS (other than 0) >NBC 3 There are 3 different values of NBC (other than 0) >XYZ 0 There are no different value of XYZ (other than 0) >I would do it like this:
SELECT DISTINCT cuTest.Field1, ISNULL(cuCount.Count,0) FROM cuTest LEFT JOIN (SELECT cuCount.Field1, COUNT(DISTINCT(cuCount.Field2)) AS Count FROM cuTest cuCount WHERE cuCount.Field2 > 0 GROUP BY cuCount.Field1) AS cuCount ON cuCount.Field1 == cuTest.Field1