Summing across Groups

[jello212]

I'm writing a report for a medical office and the patients can see any of the doctors within the office. They want a report that will appear similar to:

Dr 1
Patient A 10 total visits 3 visits for Dr1 30%
Patient B 15 total visits 3 visits for Dr1 20%
Dr 2
Patient A 10 total visits 7 visits for Dr2 70%
Patient B 15 total visits 5 visits for Dr2 33%
Patient C 7 total visits 7 visits for Dr2 100%
etc


I've grouped on Dr and then on Patient. And I can get the # of visits for each patient for each Dr. I can't get the # of visits for the patient regardless of Dr, though.

Any ideas?
p.s. Crystal 11

 

[lbass]

You could create a SQL expression like this:

(
select count(`visitID`)
from table A
where A.`patientID` = table.`patientID`
)

If you have certain selection criteria (e.g., period of time, you would need to add that to the where clause also.

-LB

 

[jello212]

You rock.