SELECT `sc`.`city_name`,`sc`.`city_id`,`mft`.`facility_type`,`mft`.`facility_code`,`mft`.`facility_name`,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` , ma.facility_type as `facilitytype` , ma.facility_name as `facilityname` FROM `master_facility_type` AS `mft` LEFT JOIN `master_asset_temp` AS `ma` ON (`mft`.`city_id` = `ma`.`district_name` AND `mft`.`facility_code` = `ma`.`facility_type` AND `ma`.`facility_name` = `mft`.`facility_id`) LEFT JOIN `setup_city` AS `sc` ON (`sc`.`city_id` = `mft`.`city_id`) where `sc`.`state_id`='3' GROUP BY `sc`.`city_name`,`mft`.`facility_type`,`mft`.`facility_name`