Using a recursive CTE over a multi-level bill of materials, how do you total each component's required quantity?

## The BOM shape A bill of materials is the classic weighted hierarchy. One table, one row per edge: ```sql -- bom(parent_id, component_id, quantity) -- (100, 200, 2) a widget needs 2 gearboxes -- (200, 300, 3) a gearbox needs 3 bolts ``` Unlike an org chart, the edge carries data. Traversal alone answers "which parts go into this product"; the interesting question is "how many of each", and that requires arithmetic along the way. Note also that a BOM is generally a directed acyclic graph rather than a tree: the same bolt legitimately appears under several subassemblies, so a component can be reached by more than one path. ## Multiply down, sum across Two different operations, in two different places. ```sql WITH RECURSIVE explosion (component_id, qty) AS ( SELECT b.component_id, b.quantity FROM bom b WHERE b.parent_id = 100 -- the product being built UNION ALL SELECT b.component_id, e.qty * b.quantity FROM bom b JOIN explosion e ON b.parent_id = e.component_id ) SELECT component_id, SUM(qty) AS total_qty FROM explosion GROUP BY component_id ORDER BY component_id; ``` **Multiplication happens inside the recursion.** `e.qty` is the accumulated requirement of the parent row — how many of that subassembly one product needs — and `b.quantity` is how many of the child each subassembly needs. Their product is how many of the child one product needs through this branch. With 2 gearboxes per widget and 3 bolts per gearbox, the bolt row carries 6. **Summation happens outside the recursion.** The CTE emits one row per *path* to a component. Grouping and summing in the outer query folds those paths together into the answer a purchasing system wants. Trying to aggregate inside the recursive member is not merely awkward — an aggregate over the recursive reference is not permitted, because the CTE is not complete while it is still being computed. ## Why UNION ALL, not UNION Suppose the widget contains two different subassemblies that each need 3 of the same bolt. Both paths produce the row `(bolt, 6)`. `UNION` would eliminate one as a duplicate and the total would come out 6 instead of 12 — an undercount that no error message announces. `UNION ALL` keeps every path's contribution, which is exactly what the outer `SUM` needs. The general rule follows from the shape of the data: whenever the carried value is a contribution to be aggregated, duplicates are meaningful. ## Diamonds and paths Because a component can be reached multiple ways, the pre-aggregation result is per-path, not per-component. That is often useful on its own: carrying a path column tells you *where* a requirement comes from, which is what a planner asks when a total looks wrong. Adding a depth column gives the indented BOM view. Both are the same technique as any hierarchy walk; the only BOM-specific part is the multiplication. If you want only the leaf components (raw materials, not subassemblies), filter the final result to components that never appear as a `parent_id` — a `NOT EXISTS` against the same table — rather than trying to express it inside the recursion. ## Cycles are a real risk here A BOM that transitively contains itself is corrupt data, and it is more common than in an org chart because BOMs are edited by many people and imported from suppliers. Unguarded, the explosion never terminates, and because quantities are multiplying the numbers also explode. Carry an id path and add the visited-node predicate to the recursive member, or use the standard `CYCLE` clause where the engine implements it. Treat a detected cycle as an alert, not as something to prune quietly — a self-containing part will produce wrong purchase orders long before anyone reads the query. ## Fractions and types Quantities are often non-integer (0.5 metres of cable) and multiply many levels deep. Use an exact numeric type; accumulating products in a binary floating-point column across five levels gives answers that fail to reconcile with anyone else's arithmetic, and in a manufacturing context that is a real reconciliation problem, not a rounding curiosity. ## Common mistakes Adding instead of multiplying inside the recursion, which produces a meaningless sum of per-level quantities. Aggregating inside the recursive member. Using `UNION` and undercounting shared components. Forgetting that the pre-aggregation rows are per-path and reporting them directly. And walking a supplier-fed BOM with no cycle guard.