>>>>>>>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
>>
>
>Your code will probably work (I have not tested it). But it looks, kind of, over complicated.
>
>The actual case I have has the following code:
>
>select FIELD1, COUNT( DISTINCT( FIELD2 ) ) AS TotCount From MyTable group by FIELD1
>
>
>The above SQL Select (of course the above one is simplified) worked for many year. But I noticed that it would always add an extra 1 to the TotCount (counting FIELD2 = 0 too). So, I was looking for a way to modify the DISTINCT() to exclude the 0 values.
>Naomi's approach works well:
>
>
> count( distinct( case when FIELD2!=0 then FIELD2 end ) ) as TotCount
>
The reason why I often use subselect is because the query should run also in VFP. You never know if you need to port queries into another database at one time.
I never knew about using case with NULL, it's very neat, but does not work in VFP. I also have not seen this in MySQL, but I might be wrong.
Christian Isberner
Software Consultant