Yeah Roll up works here. I have already tried.. but it is not working in inner query. So I have taken another outer select query and got the desired result.. thank u