>>>>Please Note: VFP 8
>>>>
>>>
>>>PS: Both queries should return wrong count but I don't know what you really want to count (couldn't visualize).
>>>
>>>Maybe this one:
>>>
SELECT cust.customer, COUNT(fr.custshipm) ;
>>> FROM crsAllCust cust;
>>> LEFT JOIN crsFillRate fr ON cust.customer = fr.custshipm AND EMPTY(fr.PartOrders);
>>> GROUP BY cust.customer
>>>
>>>Cetin
>>
>>The original query actually had COUNT(fr.custshipm). I tested with COUNT(*) and got the same results. And that was the code I cut and pasted. I also tried it with COUNT(*) and COUNT(fr.custshipm) and both fields had the same values.
>
>Count(*) inlcudes NULL values into the result and Count(Field) excludes the NULL values. This is the main difference.
>
>So, Count(*) should have included records with no match and count(RightTable.Field) should have only counted records that match.
Try now
>
SELECT cust.customer, COUNT(fr.custshipm), count(*) as AllCount, ;
SUM (IIF(ISNULL(fr.CustShipM),0,1)) as OnlyMatchedRates, ;
SUM (IIF(ISNULL(fr.CustShipM),1,0)) as CustomersNoRates, ;
>>> FROM crsAllCust cust;
>>> LEFT JOIN crsFillRate fr ON cust.customer = fr.custshipm AND EMPTY(fr.PartOrders);
>>> GROUP BY cust.customer
If it's not broken, fix it until it is.
My Blog