Small update

This count the records that are unique per Doctor

SELECT  count(*) as llCount;
        FROM MYTABLE;
  WHERE MYTABLE.cstatus <> "XXX" ;
        AND BETWEEN(TTOD(MYTABLE.ddate), tDateFrom, tDateTo) ;
  GROUP BY docid;
  INTO  CURSOR curDocTot

This count the records that are unique per Doctor and caddress

SELECT  count(*) as llCount, UPPER(caddress) as Address;
        FROM MYTABLE;
  WHERE MYTABLE.cstatus <> "XXX" ;
        AND BETWEEN(TTOD(MYTABLE.ddate), tDateFrom, tDateTo) ;
  GROUP BY docid,Address;
  INTO  CURSOR curDocTot

>
>
>>Ok, I have a sql statement to count all the records that are unique per Doctor which looks similar to this...
>>
>>select doctor
>>do while !eof()
>> llcount = 0
>>
>> SELECT count(distinct(UPPER(ALLTRIM(MYTABLE.caddress)))) as llCount
>> FROM MYTABLE WHERE MYTABLE.docid = doctor.docid
>> AND MYTABLE.cstatus <> "XXX"
>> AND BETWEEN(TTOD(MYTABLE.ddate), tDateFrom, tDateTo)
>> INTO CURSOR curDocTot
>>
>> select newtable
>> append blank
>> replace newtable.count with llCount
>>
>> skip in doctor
>>
>>enddo
>>
>>Currently the MYTABLE contains more than 50K records. I also have an index in MYTABLE.ddate. My problem here is the speed of getting the NEWTABLE being populated, as I see it the bottle neck seems on the SELECT COUNT statment. Is there anything I can do to speed up that SELECT COUNT statment here? Please advice.
>>TIA!