SETTDATE PORTFOLIO BANKNAME NOFIND FIND TOTRECORDS FINDRATE ------------------------------------------------------------------ {07/24/00} four BankFour 1 0 1 100.000 {07/24/00} one BankOne 0 1 1 0.000 {07/24/00} two BankTwo 1 1 2 50.000To get this, you could do the following:
Select hitrate1.settdate, hitrate1.portfolio, hitrate1.bankname, ; COUNT(ISNULL(newmid)) AS nofind, 0 as find ; FROM hitrate1 ; WHERE ISNULL(newmid)=.T. ; GROUP BY bankname, settdate ; UNION ; Select hitrate1.settdate, hitrate1.portfolio, hitrate1.bankname, ; 0 as nofind, COUNT(!ISNULL(newmid)) AS FIND ; FROM hitrate1 ; WHERE ISNULL(newmid)=.F. ; GROUP BY bankname, settdate ; INTO TABLE hitunion Select bankname, portfolio, settdate, ; Sum(find) as find, Sum(nofind) as nofind ; from hitunion ; group by bankname, portfolio, settdate ; into table hitsums Select *, (FIND+nofind) AS totrecords, ; (FIND/(FIND+nofind))*100 AS findrate ; FROM hitsums ; ORDER BY bankname, portfolio, settdate ; INTO TABLE hitrate4HTH