SQL Server 2008: use AVG() and an inner join
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).