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 = 558
AND c.category_id != 559
ORDER BY
product_count DESC
LIMIT
16;