I have a self-referencing table with content like this:
Self-referencing parent table
ID ParentID Name
---------------------
1 John
2 1 Mike
3 2 Erin
4 1 Janie
5 Eric
6 5 Peter
The tree hierarchy should look like this
And a child table that stores the leaf of parent table that looks like this:
ID Sales
3 100
3 100
4 200
4 200
6 300
6 300
6 300
I'm trying to roll-up the sum from the leaf node up to the hierarchy so it would return as ..
ID Name Sum
1 John 800
2 Mike 200
3 Erin 200
4 Janie 400
5 Eric 900
6 Peter 900
Any ideas how to achieve this in sql 2008? Thanks in advance.
EDIT All aggregation moved out of the CTE
WITH
tree AS
(
SELECT
id AS root_id,
name AS root_name,
id AS leaf_id
FROM
yourTreeTable
UNION ALL
SELECT
tree.root_id AS root_id,
tree.name AS root_name,
yourTreeTable.id AS leaf_id
FROM
tree
INNER JOIN
yourTreeTable
ON tree.leaf_id = yourTreeTable.ParentID
)
SELECT
tree.root_id,
tree.root_name,
COALESCE(SUM(yourScoresTable.score), 0) AS total
FROM
tree
LEFT JOIN
yourScoresTable
ON yourScoresTable.ID = tree.leafID
GROUP BY
tree.root_id,
tree.root_name
Here it is:
Let's supose this schema:
create table #parent (
ID int,
ParentID int,
Name varchar(50) );
create table #child (
ID int,
Sales int );
The query is self explained:
WITH
tree AS
(
SELECT
id as id_parent,
id as id
FROM
#parent
UNION ALL
SELECT
tree.id_parent as id_parent,
#parent.id AS id
FROM
tree
INNER JOIN
#parent
ON tree.id = #parent.ParentID
)
SELECT
#parent.id,
#parent.name,
COALESCE(SUM(#child.Sales), 0) AS total
FROM
#parent
LEFT JOIN
tree
ON #parent.ID = tree.id_parent
LEFT JOIN
#child on tree.id = #child.id
GROUP BY
#parent.id,
#parent.name
CTE returns a list of 'leafs' for each employee (#parent), then query sums all sales for this 'leafs'. You can test it running.
EDITED
Query is fixed.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With