How do you approach a query that needs several layers of logic at once?
You break it into layers, and you name each layer, so that you can see the shape of the whole thing before you write it.
So what does that look like in practice? Four steps, and you should do them in this order.
The first step is to write down what the answer looks like. Which columns, and in which order. That tells you what the last query has to produce.
The second step is to write the simplest query that gives the right number of rows. That is usually the join, without any filtering or grouping on top.
The third step is to add the calculation, which is usually the group by and the aggregate function.
The fourth step is to add the filter on the calculation, which is the HAVING clause.
step 1 : which columns go in the answer?
step 2 : which rows do I need? (the join)
step 3 : what do I calculate per group? (the group by and the sum)
step 4 : which groups do I keep? (the having)
There are two mistakes that people make here, and both of them come from doing the steps in the wrong order.
The first one is putting the filter in the wrong place. If the filter is on a calculation, it has to go in the HAVING clause. If it is on a column, it goes in the WHERE clause, and it is cheaper there.
The second one is losing rows that you meant to keep. A join that is not a left join throws away the rows with no partner, and when you are building up a report layer by layer, that is often the row you wanted.
So the rule to remember is this. Build it one layer at a time and check the number of rows after each layer, so that you always know which step changed the count.