Without knowing what data you actually expect to get, we can't help you with specifics on optimization - but two things do occur:
1) Format your queries. one long line of text is not easy to work out: break it into multiple lines so it's a lot easier to read and it becomes more maintainable, as well as better for the people who didn't throw it together!
2) At a guess, you probably need to start by looking at
SQL Joins[
^] as well as (or instead of )
SQL GROUP BY[
^]
I'd start by taking your sample data and deciding what you want to produce, and where each element of the output should come from. Then look at JOINing and GROUPing instead of multiple subqueries.