Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE category (id int, is_base_template bool, "userId" int, "parentCategoryId" int);
INSERT INTO category VALUES
(1, true , NULL, NULL),
(2, true , NULL, NULL),
(3, false, 1   , NULL),
(4, false, 1   , NULL),
(5, false, NULL, 4   ),
(6, false, NULL, 3   );

CREATE TABLE category_entries_entry ("categoryId" int, "entryId" int);
INSERT INTO category_entries_entry VALUES
(4, 1),
(5, 5),
(5, 6),
(4, 7),
(3, 8),
(6, 9);
6 rows affected

6 rows affected

-- list only root categories, counts include subcategories
WITH root AS (
   SELECT id AS cat_id, id AS sub_id
   FROM   category
   WHERE  is_base_template = false
   AND    "userId" = 1
   )
SELECT c.cat_id, count(*)::int AS entries_in_cat
FROM  (
   TABLE root
   UNION ALL
   SELECT r.cat_id, c.id
   FROM   root     r
   JOIN   category c ON c."parentCategoryId" = r.cat_id
   ) c
JOIN   category_entries_entry e ON e."categoryId" = c.sub_id
GROUP  BY c.cat_id;


cat_id
entries_in_cat


4
4

3
2


-- list root categories, and subcategories *additionally*
WITH root AS (
   SELECT id AS cat_id, id AS sub_id
   FROM   category
   WHERE  is_base_template = false
   AND    "userId" = 1
   )
, ct AS (
   SELECT c.cat_id, c.sub_id, count(*)::int AS ct
   FROM  (
      TABLE root
      UNION ALL
      SELECT r.cat_id, c.id AS sub_id
      FROM   root     r
      JOIN   category c ON c."parentCategoryId" = r.cat_id
      ) c
   JOIN   category_entries_entry e ON e."categoryId" = c.sub_id
   GROUP  BY c.cat_id, c.sub_id
   )
SELECT cat_id, sum(ct)::int AS entries_in_cat
FROM   ct
GROUP  BY 1

UNION ALL
SELECT sub_id, ct
FROM   ct
WHERE  cat_id <> sub_id;


cat_id
entries_in_cat


4
4

3
2

5
2

6
1