Calculating average for each unique ID 1

[chiefman]

I am creating a query that has to calculate the average time that a hospital room sits empty before the next case is brought in. I need the average for each room and right now my query is giving me the average overall, instead of by room. Here's the query:

SELECT PerMonInput.OR_Suite_Number, PerMonInput.Turnover_Time, Count(ORNumber.ORID) AS ORCount, Format([Turnover_Time]/[ORID],"hh:nn:ss") AS TOAVg
FROM ORNumber INNER JOIN PerMonInput ON ORNumber.ORID = PerMonInput.OR_Suite_Number WHERE [ORID] =[OR_Suite_Number] 
GROUP BY PerMonInput.OR_Suite_Number, PerMonInput.Turnover_Time, Format([Turnover_Time]/[ORID],"hh:nn:ss")
ORDER BY PerMonInput.OR_Suite_Number;

Does anyone know how to calculate the average for each room? It would help me out a lot. Thanks.

 

[Golom]

Can you explain your fields?

What does Turnover_Time contain?
The time vacant?
The time from one patient to the next?

I gather that ORID is something like a room number since it is required to be equal to OR_Suite_Number. Given that, is

[Turnover_Time]/[ORID]
i.e. Time divided by room number

the appropriate calculation?

Your WHERE clause is redundant since it just duplicates the ON condition of the JOIN.

 

[chiefman]

Sure. Turnover_Time is the time between patients. It has to meet other criteria to fall into this category, but it's calculated in another module. ORID is the operating room number. What I am trying to figure is the average turnover time per ORID (OR_Suite_Number). The table contains the turnover time for each unique record (it is calculated for each case as each case is entered). I just need to take the total Turnover_Time for each ORID and divide it by the number of records that contain that ORID. Normally I would just do that in code because it would be much easier, but I'm having to put this data into a graph. I added the WHERE clause to try to make something different happen. To see if it would change my results at all.

 

[PHV]

Why not simply this ?
SELECT OR_Suite_Number, Avg(Turnover_Time) AS TOAVg, Count(*) AS ORCount
FROM PerMonInput
GROUP BY OR_Suite_Number
ORDER BY 1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[chiefman]

Thanks a lot. That did it.