SELECT
c.category_id,
cd.category,
city.city,
city.link,
COUNT(DISTINCT p.product_id) AS product_count
FROM
cscart_products p
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_stocks ps ON ps.product_id = p.product_id
JOIN cscart_company_stocks cs ON cs.stock_id = ps.stock_id
JOIN cscart_custom_city city ON city.fias_id = cs.fias_id
WHERE
c.id_path LIKE '%559%'
AND p.status = 'A'
GROUP BY
city.city
ORDER BY
product_count DESC
LIMIT
16;