Try this

SELECT  count(*) as llCount, Doctor.Id;
        FROM MYTABLE;
  left  join Doctor ON MYTABLE.docid = doctor.docid;
  WHERE MYTABLE.cstatus <> "XXX" ;
        AND BETWEEN(TTOD(MYTABLE.ddate), tDateFrom, tDateTo) ;
  GROUP BY Doctor.ID;
  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!