Question: How to Count Rows in a Way that Aggregate (Unfortunately) Doesn't Work

I have a very large (> 4 million rows) data set. Let's say this data set consists of information on patients in a hospital & I need to determine the average length of stay (LOS) of my hospital's patients in the ICU. Every row of my data set represents one day & where that patient was on that day (along with other data); so if a patient was in my hospital for 2 weeks, she would have 14 records/rows. I know that if all I wanted was the average LOS in the ICU by patient, that'd be easy because I could just count the # of rows the patient was in the ICU by aggregating by patient ID #.

But in my hospital, a patient might be in and out of the ICU more than once during his/her stay and I want these two stays to be counted separately towards the value of the average. So as far as I can tell, there's nothing to aggregate on.

Any ideas?
Why don't you select cases based on both patient ID and location? Make the selection conditional on location = ICU. Then aggregate as you mention.