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

Question

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

Answer 1

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).