>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!

 SELECT docid ;
, count(distinct UPPER(ALLTRIM(caddress))) as count ;
  FROM MYTABLE ;
  WHERE cstatus <>  "XXX" ;
  AND ddate BETWEEN m.tDateFrom AND m.tDateTo ;
  GROUP BY 1;
  INTO CURSOR newtable