@andrewCharlz,_Asif,Tomas,deepak,aravinth04
Thanks guys for your replies!!
Sorry i didnt have the time to implement your fix.
But did a fix of my own,
i put the result set from
SELECT COUNT(city_name) YTD, mvmt.override_payee_id PAYEEID
FROM dbo.movement mvmt
INNER JOIN dbo.stop stp ON mvmt.id=stp.movement_id
WHERE STP.city_name ='CHICAGO'
GROUP BY mvmt.override_payee_id
the above query into a temp table and joined it on the temp table, that solved the issue.
Thanks for your reply.