是否有更好的方法来编写这个子查询，因为它似乎很大？

I have tried to retrieve count of the records that are depened on different conditions.I got my expected output but It seems big to me. I have tried to retrieve count of the records t