you get this working?
Why not just call the subquery in a calculated field?
as for the efficiency of the query that is another story. The way you have it written you are doing to in line aggregation per record. That will slow things done a bit. You might want to see if there is a way to remove or reduce that.