Recursive table definitions in SQL

I'm trying to learn how to use common table expressions as introduced in SQL 1999 to solve a generic bill of materials problem. I assume given the following tables:

part(id, name, price, ...)
assembly(part_id, subpart_id, quantity)

A row in the assembly table represents the fact that a part contains several instances of a subpart, which may in turn have its own subparts, and so on. The assembly table represents a tree with a given part_id as its root.

I'd like to compute the total number of occurrences of each part in the structure.

Here is my attempt using common table expressions:

WITH RECURSIVE bom(part_id, component_id, total) AS
    (SELECT id, id, 1 FROM part 
     WHERE NOT EXISTS (SELECT * FROM assembly WHERE part_id = id)   
        UNION
     SELECT assembly.part_id, bom.component_id, sum(assembly.quantity * bom.total)
     FROM assembly, bom
     WHERE assembly.subpart_id = bom.part_id
     GROUP BY assembly.part_id, bom.component_id)
SELECT component_id, total FROM bom WHERE part_id = root;

SQLite complains that recursion is not allowed through aggregates.

So, is this an SQLite restriction, or is this an SQL standard restriction?

Can anyone advise me how to rewrite this solution to accumulate more information in the common table expression and then perform the aggregate in the query that uses the table resulting table?

Many thanks, Rodney