>>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
>
>I don't understand the reason for using Distinct, can you explain what you need it for? Distinct is used to avoid doubles when you select many records, but you only want the count. This makes me confused.
>
>And you don't need the Case() to eliminate the fields holding 0, since a total on fields holding 0 will be 0 anyway.
>
>May I suggest that you show the "real" select statement, and not the "simplified" one?

I see where the confusion come in. I need to do away with COUNT().

The entire SQL select will confuse you even more. But here is the description. I am selecting all orders. Some orders have the value of 0. And I want to show in the report how many orders, of each order number - other than 0 - are in the report.
If I simply use distinct( mytable.myfield ) as TotCount then the TotCount has one extra value (for the orders that have the value of 0).

And in this case the district has nothing to do with the case of avoiding doubles.

Thank you.

UPDATE. I do want to include COUNT(). Please see my reply to myself.