Suppose that there are two table:

SAMPLES
----------------------
sample  integer (observation value list)


RANGES
---------------
Seq   AutoInc (sequence number)
LL    Integer  (low limit for range)
HL    Integer  (high limit for range)


SAMPLES         RANGES
----------------   ---------------
1         1   1   4
2         2   2   5
3         3   7   8
4         4   8  10
5
6
7
8
9
10

I want to calculate sample range frequance (lowlimit<= sample 
<HighLimit).

select A.sample, count(b.Seq) from Samples A
left outer join Ranges on (A.Sample>=B.LL) and (A.Sample<B.HL)
group by A.sample

RESULT SETS
-----------------------

SAMPLE  DBISAM  BDE
1       1       1
2       2   2
3       2       1
4       1       1
5       1       0
6       1       0
7       1       1
8       1       1
9       1       1
10      1       0 


If i use sum(b.seq) instead of count(b.seq) the result sets are equal.
 If i use count(*) instead of count(b.seq) the result sets are equal.

I run below Sql statement too:

select A.sample, count(b.Seq) from Samples A, Ranges B
where (A.Sample>=B.LL) and (A.Sample<B.HL)
group by A.sample

The result sets are equal and correct both count(b.seq) and count(*). 
But the result sets are not include zero frequance samples.

---

 Comments and Workarounds

---

The problem was with the autoinc column being used for the COUNT and the fact that autoinc columns are actually never NULL internally in DBISAM. The workaround was to perform the COUNT on a non-autoinc column in the target table of the LEFT OUTER JOIN.

---

 Resolution

---

Fixed Problem on 10/28/2001 in version 3.04 build 1