SELECT
c.category_id,
cd.category,
pfd.description,
pfd.link,
var.variant,
var.link AS variant_link,
COUNT(DISTINCT p.product_id) AS product_count
FROM
cscart_products p
LEFT JOIN cscart_products_categories pc ON pc.product_id = p.product_id
JOIN cscart_categories c ON pc.category_id = c.category_id
JOIN cscart_category_descriptions cd ON c.category_id = cd.category_id
JOIN cscart_product_features_values pfv ON pfv.product_id = p.product_id
LEFT JOIN cscart_product_features_descriptions pfd ON pfd.feature_id = pfv.feature_id
LEFT JOIN cscart_product_feature_variant_descriptions var ON var.variant_id = pfv.variant_id
WHERE
pfd.feature_id IN (
737, 741, 744, 747, 775, 776, 781, 796,
797, 807, 811
)
AND c.id_path LIKE '%740%'
AND p.status = 'A'
GROUP BY
var.variant_id
ORDER BY
product_count DESC
LIMIT
16;