我的数据库中有以下数据。我有3个表Category，Item和ItemCategories。表Category有id，name，parent_crawler_id和crawler_id。Item有id和name。ItemCategories有id，item_id和category_id。
以下是类别的示例数据：

id (pk)      name              parent_crawler_id            crawler_id
1            Fruit and Veg     null                         8899
2            Organic           8899                         88100
3            Green             88100                        88101

以下是项目的示例数据：

id(pk)       name              
1            Grapes
2            Apples

下面是ItemCategories的示例数据

id           item_id (fk)      category_id (fk)
1            1                 2
2            2                 3
3            1                 3
4            2                 2

我想对Category表进行分组，并包含其子表的计数。我想获得以下输出：

name            count
Fruit and Veg   6
Organic         4
Green           2

以下是我的查询

WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_crawler_id, crawler_id
  FROM "Category"
  
  UNION ALL
  
  SELECT c.id, c.name, c.parent_crawler_id, c.crawler_id
  FROM "Category" c
  JOIN category_tree ct ON c.parent_crawler_id = ct.crawler_id
)
SELECT ct.name, SUM(subquery.count) AS total_count
FROM category_tree ct
LEFT JOIN (
  SELECT ic.category_id, COUNT(DISTINCT ic.item_id) AS COUNT
  FROM "ItemCategories" ic
  GROUP BY ic.category_id
) subquery ON subquery.category_id = ct.id
GROUP BY ct.name
ORDER BY ct.name

我如何使用SQL和递归来实现这一点？