SELECT nsc.secondLevelCatID AS categoryID, count(nsp.productID) AS productCount, nscl.name, nscl.linkReWrite FROM ns_product nsp JOIN ns_category nsc ON nsp.categoryID = nsc.categoryID JOIN ns_category_lang nscl ON nsc.secondLevelCatID = nscl.categoryID WHERE nsp.isActive = 1 AND nsp.isDeleted = 0 AND nsc.isActive = 1 AND nscl.langID = 'en' AND nsp.shopID IN (93) AND nsc.depthLevel IN (1,2,3,4) GROUP BY nsc.secondLevelCatID ORDER BY productCount DESC