>>>>>Hi,
>>>>>
>>>>>I am trying to count unique values in the field (using SQL Select) if the value is greater than 0
>>>>>
>>>>>So, here is syntax I use (simplified) (which generates an error):
>>>>>

>>>>>select count( case when mytable.myfield = 0 then 0 else distinct( mytable.myfield ) end ) as TotCount
>>>>>

>>>>>
>>>>>What is wrong in the above syntax?
>>>>>
>>>>>TIA
>>>>
>>>>

select count( distinct  mytable.myfield ) as TotCount from myTable where mytable.myfield <> 0

>>>
>>>The SQL Select must include all records, even those that have the value of mytable.myfield equal to 0 (zero). So I cannot use the WHERE as you suggested.
>>>Please see Naomi's reply as the solution.
>>>Thank you.
>>
>>How is that different, other than doing the same thing in an unnecessarily peculiar way?
>>
>>Here is demo link:
>>
>>https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=5ff29037f75de1d5939272329523c434
>
>Here is an example of a table and the result I am trying to get. Let me know how I would apply your approach to it:
>
>

>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