WITH RECURSIVE category_hierarchy AS (
  SELECT 
    category_id, 
    parent_id 
  FROM 
    cscart_categories 
  WHERE 
    category_id IN (
      34, 43, 60, 61, 69, 160, 85, 95, 96, 101, 
      104, 113, 119, 120, 124, 131, 78
    ) 
  UNION ALL 
  SELECT 
    c.category_id, 
    c.parent_id 
  FROM 
    cscart_categories c 
    INNER JOIN category_hierarchy ch ON c.parent_id = ch.category_id
) 
SELECT 
  ch.parent_id AS category_id, 
  SUM(pc.products_count) AS products_count 
FROM 
  category_hierarchy ch 
  JOIN (
    SELECT 
      pc.category_id, 
      COUNT(pc.product_id) AS products_count 
    FROM 
      cscart_products_categories AS pc 
      LEFT JOIN cscart_products AS p ON p.product_id = pc.product_id 
      LEFT JOIN cscart_companies AS c ON p.company_id = c.company_id 
    WHERE 
      p.status = 'A' 
      AND p.paused = 'N' 
      AND c.status = 'A' 
      AND (
        p.amount >= p.min_qty 
        OR (
          p.min_qty IS NULL 
          AND p.amount > 0
        )
      ) 
    GROUP BY 
      pc.category_id
  ) pc ON ch.category_id = pc.category_id 
GROUP BY 
  ch.parent_id 
HAVING 
  ch.parent_id > 0;