You've been given some answers that should work. For an explanation of why what you originally tried didn't work, look at the section "Aggregating both parents and children" in this paper on my website: http://tomorrowssolutionsllc.com/Materials/SQLProblems.PDF.