SELECT COUNT(L.listing_id) FROM listings AS L LEFT JOIN categories AS CATISL ON L.island=CATISL.id LEFT JOIN categories AS CATLOC ON L.location=CATLOC.id LEFT JOIN categories AS CATDIS ON L.district = CATDIS.id LEFT JOIN media AS M ON M.parent_listing = L.listing_id AND M.type='photo' WHERE L.cms_status='complete' AND L.category='residential' GROUP BY listing_id ORDER BY hide ASC, expiry DESC, listing_id ASC