41. What is the average number of medications prescribed per visit for each physician?
Somewhat tricky because physician is in the VisitProcedure table which can have multiple entries.
a. Compute Number of meds per visit.
Q1:
SELECT VisitMedications.VisitID, Count(VisitMedications.DrugSeqNo) AS CountOfDrugSeqNo
FROM VisitMedications
GROUP BY VisitMedications.VisitID;
b. Find Physician and Visit
Q2:
SELECT Employee.EmployeeID, Employee.LastName,
Employee.EmployeeCategory, VisitProcedures.VisitID,
Count(VisitProcedures.VisitProcedureID) AS CountOfVisitProcedureID
FROM Employee INNER JOIN VisitProcedures ON Employee.EmployeeID = VisitProcedures.EmployeeID
GROUP BY Employee.EmployeeID, Employee.LastName, Employee.EmployeeCategory, VisitProcedures.VisitID
HAVING (((Employee.EmployeeCategory)="Physician"));
c. Compute average of med count by physician.
SELECT Employee.EmployeeID, Employee.LastName,
Avg(Q1.CountOfDrugSeqNo) AS AvgOfCountOfDrugSeqNo
FROM Q1 INNER JOIN (Q2 INNER JOIN Employee
ON Q2.EmployeeID = Employee.EmployeeID) ON Q1.VisitID = Q2.VisitID
GROUP BY Employee.EmployeeID, Employee.LastName
ORDER BY Avg(Q1.CountOfDrugSeqNo) DESC;