SQLite recursive CTE to calculate number of items in list (of lists)

Question

Each List can have multiple ListItems (1 --> n). And a list can have a parent list.

      List 1
       |
       |-- List Item 1
       |
       |-- List 2  
       |     |              
       |     |-- Liste Item 3
       |
       |-- List 3

I would like to know how many items there are in List1 + its child lists(List2 + List3 + ... etc), so for List1 the totalNumberOfItems is 6.

The table List looks like this

id	name	parentId
1	List 1
2	List 2	1
3	List 3	2

And table ListItem looks like this

id	name	parentId
1	Item 1	1
2	Item 2	1
3	Item 3	2
4	Item 4	1
5	Item 5	2
6	Item 6	3

The column "parentId" in ListItem points to the List that the item belongs to. When I run the SQLite query below...

    WITH RECURSIVE x (id, name, parentId, totalNumberOfItems)
    AS (
    SELECT ID, Name, parentId, 
           (SELECT COUNT(*) FROM listitem WHERE parentId = list.id) 
           as totalNumberOfItems
    FROM List 
    WHERE parentId is NULL
    UNION ALL
    SELECT e.id, e.name, e.parentId, 
          (SELECT COUNT(*) FROM listitem WHERE parentId = e.id) 
          as totalNumberOfItems
    FROM list e INNER JOIN x on e.parentId = x.ID
    )
    SELECT * FROM x

Then I get the following result:

Below is a script that allows to create the Db-Schema and insert the data.

BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "List" (
    "id"    INTEGER NOT NULL,
    "name"  TEXT NOT NULL,
    "parentId"  INTEGER,
    PRIMARY KEY("id" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "ListItem" (
    "id"    INTEGER NOT NULL,
    "name"  TEXT NOT NULL,
    "parentId"  INTEGER NOT NULL,
    PRIMARY KEY("id" AUTOINCREMENT),
    FOREIGN KEY("parentId") REFERENCES "List"("id")
);
INSERT INTO "List" ("id","name","parentId") VALUES (1,'List1',NULL),
 (2,'List2',1),
 (3,'List3',2);
INSERT INTO "ListItem" ("id","name","parentId") VALUES (1,'Item1',1),
 (2,'Item2',1),
 (3,'Item3',2),
 (4,'Item4',1),
 (5,'Item5',2),
 (6,'Item6',3);
COMMIT;

Answer 1

Use a recursive CTE that returns the ids of the list that you want and all its children below up the lowest level.

Then aggregate in the table ListItem by using the results of that query:

WITH cte AS (
  SELECT id FROM List WHERE name = 'List1' 
  UNION ALL
  SELECT l.id
  FROM List l INNER JOIN cte c
  ON l.parentId = c.id
)
SELECT COUNT(*) count FROM ListItem WHERE parentId IN cte;

See the demo.