SQLTeam.com | Weblogs | Forums

Sum values of child-lines and add it to the parent line


#1

Imagine the below table: I have multiple lines where one line is always the child of another, except lines with level=1 don't have a parent.
What I need is to make the sum all values where level > 2 and add that sum to the value of the parent line where level = 2.

In the below example, I'll have to sum lines 3 to 8 and add it to line 2 etc...

At last, I also need a total sum.
PS: this is a nice and clean example. In real, the lines are not ordered per sub-part...


#2

Without having sample data to test, it is hard to write a query. For someone to be able to use the data that you posted, they will have to create a test table, read the data off the screenshot to populate the table and then write the query. You can help them and get faster/better/accurate answers by posting the script to create and populate the sample table. There is some guidance on how to do that on this page.

Having said that, following is a query that might help you get started. I haven't tested it because I didn't create the test table and data. So...

;WITH cte AS
(
	SELECT *, Value AS Tot FROM Tbl t WHERE 
	    	NOT EXISTS (SELECT * FROM Tbl t2 WHERE t2.parent = t.id)
	UNION ALL
	SELECT t.*,Tot+t.Value AS Tot FROM Tbl t INNER JOIN cte c ON c.parent = t.id
)
SELECT id, Level, SUM(Tot) FROM cte WHERE Level = 2 
	GROUP BY GROUPING SETS ((id, Level),());

#3

Hi JamesK,
Sorry for not having followed the forum rules.

Here is an SQL statement to create a table with my example data:

DROP TABLE Tbl;
CREATE TABLE Tbl(
   ID     INTEGER  NOT NULL PRIMARY KEY 
  ,Parent INTEGER 
  ,Type   VARCHAR(23) NOT NULL
  ,Level  INTEGER  NOT NULL
  ,Value  INTEGER  NOT NULL
);
INSERT INTO Tbl(ID,Parent,Type,Level,Value) VALUES (1,NULL,'Main',1,0);
INSERT INTO Tbl(ID,Parent,Type,Level,Value) VALUES (2,1,'Sub1',2,0);
INSERT INTO Tbl(ID,Parent,Type,Level,Value) VALUES (3,2,'Detail1',3,10);
INSERT INTO Tbl(ID,Parent,Type,Level,Value) VALUES (4,3,'DetailOfDetail1',4,5);
INSERT INTO Tbl(ID,Parent,Type,Level,Value) VALUES (5,3,'DetailOfDetail2',4,10);
INSERT INTO Tbl(ID,Parent,Type,Level,Value) VALUES (6,5,'DetailOfDetailOdDetail1',5,5);
INSERT INTO Tbl(ID,Parent,Type,Level,Value) VALUES (7,2,'Detail2',3,20);
INSERT INTO Tbl(ID,Parent,Type,Level,Value) VALUES (8,2,'Detail3',3,50);
INSERT INTO Tbl(ID,Parent,Type,Level,Value) VALUES (9,1,'Sub2',2,0);
INSERT INTO Tbl(ID,Parent,Type,Level,Value) VALUES (10,9,'Detail4',3,20);
INSERT INTO Tbl(ID,Parent,Type,Level,Value) VALUES (11,10,'DetailOfDetail3',4,5);
INSERT INTO Tbl(ID,Parent,Type,Level,Value) VALUES (12,10,'DetailOfDetail4',4,10);
INSERT INTO Tbl(ID,Parent,Type,Level,Value) VALUES (13,9,'Detail5',3,10);
INSERT INTO Tbl(ID,Parent,Type,Level,Value) VALUES (14,9,'Detail6',3,5);
INSERT INTO Tbl(ID,Parent,Type,Level,Value) VALUES (15,14,'DetailOfDetail5',4,5);
INSERT INTO Tbl(ID,Parent,Type,Level,Value) VALUES (16,15,'DetailOfDetailOdDetail2',5,15);
INSERT INTO Tbl(ID,Parent,Type,Level,Value) VALUES (17,1,'Sub3',2,0);
INSERT INTO Tbl(ID,Parent,Type,Level,Value) VALUES (18,17,'Detail7',3,5);
INSERT INTO Tbl(ID,Parent,Type,Level,Value) VALUES (19,18,'DetailOfDetail5',4,20);
INSERT INTO Tbl(ID,Parent,Type,Level,Value) VALUES (20,19,'DetailOfDetailOdDetail2',5,10);
INSERT INTO Tbl(ID,Parent,Type,Level,Value) VALUES (21,17,'Detail8',3,25);

Secondly, many thanks for your reply!

I tested your solution with this example data and it's almost OK I guess.
For some kind of reason, I get this (FYI: I added the fieldname 'Type'):

Line / id / Type  / Level / Sum
1    /  2 / Sub 1 /   2   / 110
2    /  9 / Sub 2 /   2   /  90
3    / 17 / Sub 3 /   2   /  60
4    /NULL/ NULL  / NULL  / 260

So the first sum is 10 too many, the second is 20 too many and the third one is OK.

To solve this, I tried to understand your solution. The first part of the common table expression, gives all the lines that have no children. The second part is the part I don't understand well, because it refers to itself (INNER JOIN cte...)? But I did notice that 2 lines are appearing twice:

I tried to change some things, but I only got errors as I have absolutely no clue...


#4

The query I posted earlier won't work if there are level 3 or deeper nodes that have more than one child. Try the following - it does work for the sample data you posted.

;WITH cte1 AS
(
	SELECT a.*,COALESCE(b.N,0) AS N
	FROM
		Tbl a
		OUTER APPLY
		(
			SELECT COUNT(*) N
			FROM Tbl b
			WHERE a.id = b.parent
		)b
),
cte AS
(
	SELECT *, 1.E * Value AS Tot FROM cte1 t WHERE 
	    	NOT EXISTS (SELECT * FROM cte1 t2 WHERE t2.parent = t.id)
	UNION ALL
	SELECT t.*,Tot+ (1.E * t.VALUE)/t.N AS Tot 
	FROM cte1 t INNER JOIN cte c ON c.parent = t.id
)
SELECT id, Level, SUM(Tot) FROM cte WHERE Level = 2 
	GROUP BY GROUPING SETS ((id, Level),());		

The cte with a UNION ALL and reference to the cte in the part after the UNION ALL is a recursive cte. It is very useful in situations such as this where you don't know the depth of the tree in advance, and so the queries need to be recursive.


#5

Hi JamesK,

Your solution worked fine! Thanks for that.
Unfortunately, I got extra requirements: a new column "Value2" was added.
I tried to edit your query like this:

WITH cte1 AS
(
	SELECT a.*,COALESCE(b.N,0) AS N
	FROM
		Tbl a
		OUTER APPLY
		(
			SELECT COUNT(*) N
			FROM Tbl b
			WHERE a.id = b.parent
		)b
),
cte AS
(
	SELECT *, 1.E * Value AS Tot 
		    , 1.E * Value2 AS Tot2 
		    FROM cte1 t WHERE 
	    	NOT EXISTS (SELECT * FROM cte1 t2 WHERE t2.parent = t.id)
	UNION ALL
	SELECT t.*,
		Tot+ (1.E * t.VALUE)/t.N AS Tot ,
		Tot2+ (1.E * t.VALUE2)/t.N AS Tot2
	FROM cte1 t INNER JOIN cte c ON c.parent = t.id
)
SELECT id, Level, SUM(Tot), SUM(Tot2) FROM cte WHERE Level = 2 
	GROUP BY GROUPING SETS ((id, Level),());

But I get weird results on Value2...:

Probably, it has something to do with this part:

	SELECT *, 1.E * Value AS Tot 
		    , 1.E * Value2 AS Tot2

But I'm not familiar with "1.E", so I don't know well what to change there...
I thought it would refer to the 5th column of table Tbl, which is value, but when I tried "1.F" for Value2, I got a lot of errors...

Herewith new example data:

DROP TABLE Tbl;
CREATE TABLE Tbl(
   ID     INTEGER  NOT NULL PRIMARY KEY 
  ,Parent INTEGER 
  ,Type   VARCHAR(23) NOT NULL
  ,Level  INTEGER  NOT NULL
  ,Value  INTEGER  NOT NULL
  ,Value2  INTEGER  NOT NULL
);
INSERT INTO Tbl(ID,Parent,Type,Level,Value,Value2) VALUES (1,NULL,'Main',1,0,10);
INSERT INTO Tbl(ID,Parent,Type,Level,Value,Value2) VALUES (2,1,'Sub1',2,0,25);
INSERT INTO Tbl(ID,Parent,Type,Level,Value,Value2) VALUES (3,2,'Detail1',3,10,0);
INSERT INTO Tbl(ID,Parent,Type,Level,Value,Value2) VALUES (4,3,'DetailOfDetail1',4,5,0);
INSERT INTO Tbl(ID,Parent,Type,Level,Value,Value2) VALUES (5,3,'DetailOfDetail2',4,10,0);
INSERT INTO Tbl(ID,Parent,Type,Level,Value,Value2) VALUES (6,5,'DetailOfDetailOdDetail1',5,5,0);
INSERT INTO Tbl(ID,Parent,Type,Level,Value,Value2) VALUES (7,2,'Detail2',3,20,25);
INSERT INTO Tbl(ID,Parent,Type,Level,Value,Value2) VALUES (8,2,'Detail3',3,50,0);
INSERT INTO Tbl(ID,Parent,Type,Level,Value,Value2) VALUES (9,1,'Sub2',2,0,50);
INSERT INTO Tbl(ID,Parent,Type,Level,Value,Value2) VALUES (10,9,'Detail4',3,20,10);
INSERT INTO Tbl(ID,Parent,Type,Level,Value,Value2) VALUES (11,10,'DetailOfDetail3',4,5,0);
INSERT INTO Tbl(ID,Parent,Type,Level,Value,Value2) VALUES (12,10,'DetailOfDetail4',4,10,0);
INSERT INTO Tbl(ID,Parent,Type,Level,Value,Value2) VALUES (13,9,'Detail5',3,10,5);
INSERT INTO Tbl(ID,Parent,Type,Level,Value,Value2) VALUES (14,9,'Detail6',3,5,0);
INSERT INTO Tbl(ID,Parent,Type,Level,Value,Value2) VALUES (15,14,'DetailOfDetail5',4,5,0);
INSERT INTO Tbl(ID,Parent,Type,Level,Value,Value2) VALUES (16,15,'DetailOfDetailOdDetail2',5,15,0);
INSERT INTO Tbl(ID,Parent,Type,Level,Value,Value2) VALUES (17,1,'Sub3',2,0,40);
INSERT INTO Tbl(ID,Parent,Type,Level,Value,Value2) VALUES (18,17,'Detail7',3,5,0);
INSERT INTO Tbl(ID,Parent,Type,Level,Value,Value2) VALUES (19,18,'DetailOfDetail5',4,20,10);
INSERT INTO Tbl(ID,Parent,Type,Level,Value,Value2) VALUES (20,19,'DetailOfDetailOdDetail2',5,10,0);
INSERT INTO Tbl(ID,Parent,Type,Level,Value,Value2) VALUES (21,17,'Detail8',3,25,10);

#6

I see the problem. I was making it unnecessarily complex, and the complexity was not giving the correct results either. A simpler way would be to start with the root level (or level 2 in your case which is what you are interested in) and aggregate all the children like shown below

;with cte as
(
	select *, id as id2 from tbl where level = 2
	union all
	select t.*, c.id2
	from #tbl t inner join cte c on c.id = t.Parent
)
select 
	id2, sum(value) Value, sum(value2) Value2 
from
	cte
group by
	id2;

#7

Hi James,

Sorry for my late reply.
I just wanted to tell you that this is indeed a solution to my problem.

Many thanks for that!