SELECT 
  c.category_id, 
  cd.category, 
  (
    SELECT 
      COUNT(DISTINCT p.product_id) 
    FROM 
      cscart_products p 
      JOIN cscart_products_categories pc ON pc.product_id = p.product_id 
      JOIN cscart_categories sub_c ON pc.category_id = sub_c.category_id 
    WHERE 
      (
        sub_c.id_path LIKE CONCAT(c.id_path, '/%') 
        OR sub_c.category_id = c.category_id
      ) 
      AND p.status = 'A'
  ) AS product_count 
FROM 
  cscart_categories c 
  JOIN cscart_category_descriptions cd ON c.category_id = cd.category_id 
WHERE 
  c.parent_id = 597 
  AND c.category_id != 906 
ORDER BY 
  product_count DESC 
LIMIT 
  16;