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...
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),());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...
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.
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);
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;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!