SQL Server 2008: use AVG() and an inner join

Here's what I have so far. I know this works, but I need to get the average of the pulse and depressionlevel instead of returning actual values for each row:

SELECT
Patients.LastName
, PatientVisit.Pulse AS [avg Pulse]
, PatientVisit.DepressionLevel AS [avg Deplevel]
FROM Patients
INNER JOIN PatientVisit on Patients.PatientKey = PatientVisit.PatientKey
ORDER BY
Patients.LastName


SELECT Patients.LastName, 
AVG(PatientVisit.Pulse) as "avg Pulse", 
AVG(PatientVisit.DepressionLevel) as "avg Deplevel" 
from Patients 
inner join 
PatientVisit 
on Patients.PatientKey = PatientVisit.PatientKey 
GROUP BY Patients.PatientKey, Patients.LastName
order by Patients.LastName;

Important to group by PatientKey as well, so you don't have a single record for Smiths and Nguyens. It also allows extra optimisations, as it (should be) marked as unique (being the Primary Key).

StackExchange Database Administrators Q#23551, answer score: 6