When I run my query with a subquery I get Invalid column name 'Fail' What am I doing wrong? Thanks in advance
The reason you get the error is because you try to group by the rows in your subquery by the column named Fail but this column is "not yet available" to the group by clause because of it's order of execution. One way to make your query work would be to restate it like this:
This makes it somewhat ugly, but the bottom line is that you cannot use an aliased column in your group by and have to instead use the whole expression from your select statement to make it work.
<!-- Begin Edit
Actually, looking at the query in your question, the logic has a flaw, because the group by does not actually do what you need it to do. It looks like all you need is the sum of Policysin, sum of Failed and the ratio of Failed to Policysin expressed as percentage. But this means that you should not have anything in the group by except the Inceptmonth column and the aggregates should be applied in the actual calculation of that percentage. Including this calculation in the group by will not produce expected result. You query therefore has to be restated. Something like this will give you the data you need:
End Edit -->