select l.city,(select count(distinct l1.location_id) from dmslocation l1 (nolock) where l1.city=l.city) as ‘Total Record’,(select count(distinct l1.location_id) from dmslocation l1 (nolock) where l1.city=l.city and .validated_remarks=‘Valid Data’) as ‘Valid Record’,(select count(distinct l1.location_id) from dmslocation l1 (nolock) where l1.city=l.city and isnull(l1.validated_remarks,”) not in(”,‘Select’,‘Valid Data’)) as ‘Invalid Record’,(select count(distinct l1.location_id) from dmslocation l1 (nolock) where l1.city=l.city and isnull(l1.validated_remarks,”) in(”,‘Select’)) as ‘To Validate’from dmslocation l (nolock) where l.city in(‘Delhi’,‘Bangalore’) group by l.city
from city Total Record Valid Record Invalid Record
Bangalore 189532 64988 83681 40863
Delhi 206572 37531 95617 73424
dmslocation l (nolock) where l.city in(‘Delhi’,‘Bangalore’) group by l.cityTo Validateselect
count(1) from dmslocation where city=‘Delhi’ and validated_remarks not in(‘select’,‘Valid data’)
Arrao4u 