>I understand I may need to add a subquery somewhere to make that fit. Any suggestion on the best practice?
Naomi gave you the answer, but just to jump in as well....anytime you have a pattern of two or more 1-many relationships and you're doing aggregations against multiple child tables, you must break it up into multiple subqueries, or multiple temporary result sets. Otherwise you'll get over-aggregation of the answers.