SELECT `sc`.`city_name`,`sc`.`city_id` ,COUNT(`ma`.`srno`) AS `total_asset`,COUNT(CASE WHEN `ma`.`varified` = 'Pending' THEN 1 END ) AS `asset_pending`,COUNT(CASE WHEN `ma`.`varified` = 'varified' THEN 1 END ) AS `asset_varified` FROM `setup_city` AS `sc`  LEFT JOIN `master_asset_temp` AS `ma` ON (`ma`.`district_name` = `sc`.`city_id` ) where `sc`.`state_id`='3' and sc.status='Active' GROUP BY `sc`.`city_name`