SELECT 
  c.service_type_id, 
  COUNT(DISTINCT p.product_id) AS products_count 
FROM 
  cscart_products AS p 
  INNER JOIN cscart_products_categories AS pc ON pc.product_id = p.product_id 
  INNER JOIN cscart_categories AS c ON c.category_id = pc.category_id 
WHERE 
  p.company_id = 19 
  AND p.status != 'D' 
  AND c.service_type_id IS NOT NULL 
GROUP BY 
  c.service_type_id