Calculate total values for parent nodes in hierarchical data using recursive CTE

Data and Databases
1

I need help creating a SQL query that calculates the sum of all child values for each parent node in a hierarchical structure. I’m working with PostgreSQL and need to use a recursive common table expression.

Here’s my table structure:

CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    parent_dept INT,
    budget DECIMAL(10,2) DEFAULT 0,
    is_category INT,
    dept_name VARCHAR(200)
);

INSERT INTO departments VALUES
(10, NULL, 'headquarters', 0.0, 1),
(20, 10, 'operations', 0.0, 1),
(30, 10, 'current_ops', 0, 1),
(40, 30, 'sales', 15.0, 0),
(50, 30, 'marketing', 25.0, 0),
(60, 10, 'infrastructure', 0, 1),
(70, 60, 'facilities', 150.0, 0),
(80, 10, 'inventory', 35.0, 0),
(90, 10, 'equipment', 45.0, 0);

I want the query to return each parent node with the total sum of all its descendants. For example, the ‘current_ops’ department should show a total of 40.0 (sales 15.0 + marketing 25.0). The ‘headquarters’ should show the grand total of all departments below it.

How can I write a recursive CTE that traverses the tree structure and calculates these subtotals for each parent node?

2

this is tricky but def doable. start with a base case that selects all rows, then recursively join where parent_dept = dept_id. use sum() over the recursive results to get totals for each branch. don’t forget to handle leaf nodes properly - they won’t have children.

3

interesting challenge! you could try window functions with the recursive CTE. quick questions - are there any circular references in your hierarchy? and how’s performance with larger datasets?

4

I’ve built something similar for departmental dashboards. You’ll need a two-step approach with recursive CTEs. First CTE handles the hierarchy - start from root nodes where parent_dept is NULL, then join recursively on parent_dept = dept_id from the previous level. Second CTE aggregates the budget values upward from leaf nodes. For parent node totals, traverse back up the hierarchy and sum the subtree values. Just make sure your base case terminates properly and the recursive join doesn’t create infinite loops. Performance is decent for typical org charts, but definitely index dept_id and parent_dept if you’re dealing with large datasets.