July 28, 2011 at 7:48 am
Hi Folks,
I have requirement to add up an integer value in a hierarchy for each node with it's subnodes totaling the value at the root. This seems like a running total type operation but I am stymied by the parent child relationships.
Example Data:
ID, PARENT_ID, CTR
189, 0, 5
190, 189, 10
191, 189, 2
192, 191, 5
I need to calculate the totals such that I have a new integer column that contains the sum of it's CTR column with all of it's descendants.
ID, PARENT_ID, CTR, SUM_descendants
189, 0, 5, 22 (5+10+7)
190, 189, 10, 10
191, 189, 2, 7 (2+5)
192, 191, 5, 5
I think I should be able to use a RANK() and use this in a COALESCE subquery but I'm just not putting it together.
Any ideas would be helpful.
July 28, 2011 at 8:50 am
Would help if you provide DDL and data setup scripts...
Please follow the link in my signature where you will find details.
Otherwise, you will need to wait for someone who has time to do some "dirty" work for you, and it may take longer than anticipated 😀
Following the forum etiquette will guarantee prompt and helpfull responses.
July 28, 2011 at 10:41 am
My apologies vis-a-vis etiquette:
CREATE TABLE [dbo].[ancestor](
[id] [bigint] NOT NULL,
[parent_id] [bigint] NOT NULL,
[language_id] [int] NULL,
[level] [int] NULL,
[ctr] [int] NULL,
[total] [int] NULL
) ON [PRIMARY]
go
alter table ancestor add constraint pk_ancestor primary key clustered(id,parent_id)
go
insert into ancestor (id,parent_id,language_id,level,ctr) values(189,0,55,1,12)
insert into ancestor (id,parent_id,language_id,level,ctr) values(190,189,55,2,2)
insert into ancestor (id,parent_id,language_id,level,ctr) values(191,189,55,2,2)
insert into ancestor (id,parent_id,language_id,level,ctr) values(192,189,55,2,13)
insert into ancestor (id,parent_id,language_id,level,ctr) values(193,189,55,2,4)
insert into ancestor (id,parent_id,language_id,level,ctr) values(194,189,55,2,5)
insert into ancestor (id,parent_id,language_id,level,ctr) values(195,189,55,2,1)
insert into ancestor (id,parent_id,language_id,level,ctr) values(218,189,55,2,3)
insert into ancestor (id,parent_id,language_id,level,ctr) values(260,218,55,3,0)
insert into ancestor (id,parent_id,language_id,level,ctr) values(205,194,55,3,5)
insert into ancestor (id,parent_id,language_id,level,ctr) values(206,194,55,3,4)
insert into ancestor (id,parent_id,language_id,level,ctr) values(207,194,55,3,8)
insert into ancestor (id,parent_id,language_id,level,ctr) values(208,194,55,3,8)
insert into ancestor (id,parent_id,language_id,level,ctr) values(209,194,55,3,7)
insert into ancestor (id,parent_id,language_id,level,ctr) values(210,194,55,3,0)
insert into ancestor (id,parent_id,language_id,level,ctr) values(203,193,55,3,0)
insert into ancestor (id,parent_id,language_id,level,ctr) values(204,193,55,3,10)
insert into ancestor (id,parent_id,language_id,level,ctr) values(259,193,55,3,0)
insert into ancestor (id,parent_id,language_id,level,ctr) values(201,192,55,3,8)
insert into ancestor (id,parent_id,language_id,level,ctr) values(202,192,55,3,2)
insert into ancestor (id,parent_id,language_id,level,ctr) values(233,192,55,3,0)
insert into ancestor (id,parent_id,language_id,level,ctr) values(199,191,55,3,0)
insert into ancestor (id,parent_id,language_id,level,ctr) values(200,191,55,3,0)
insert into ancestor (id,parent_id,language_id,level,ctr) values(196,190,55,3,14)
insert into ancestor (id,parent_id,language_id,level,ctr) values(197,190,55,3,17)
insert into ancestor (id,parent_id,language_id,level,ctr) values(198,190,55,3,9)
insert into ancestor (id,parent_id,language_id,level,ctr) values(234,233,55,4,4)
insert into ancestor (id,parent_id,language_id,level,ctr) values(236,233,55,4,4)
insert into ancestor (id,parent_id,language_id,level,ctr) values(237,233,55,4,5)
insert into ancestor (id,parent_id,language_id,level,ctr) values(238,233,55,4,4)
insert into ancestor (id,parent_id,language_id,level,ctr) values(249,204,55,4,3)
insert into ancestor (id,parent_id,language_id,level,ctr) values(250,204,55,4,3)
insert into ancestor (id,parent_id,language_id,level,ctr) values(251,204,55,4,2)
insert into ancestor (id,parent_id,language_id,level,ctr) values(252,207,55,4,2)
insert into ancestor (id,parent_id,language_id,level,ctr) values(253,207,55,4,2)
insert into ancestor (id,parent_id,language_id,level,ctr) values(254,207,55,4,2)
insert into ancestor (id,parent_id,language_id,level,ctr) values(255,207,55,4,1)
insert into ancestor (id,parent_id,language_id,level,ctr) values(261,260,55,4,1)
insert into ancestor (id,parent_id,language_id,level,ctr) values(262,260,55,4,0)
insert into ancestor (id,parent_id,language_id,level,ctr) values(263,260,55,4,0)
go
This is a representative table and setup data. What I am trying to achieve is to update the TOTAL column with the sum of the primary key's CTR column and all of it's descendant's CTR values.
For example, PK 252/207's TOTAL would be 2 as it has no descendants but PK 207/194 would be it's CTR value 8 + the sum of all the PK's whose PARENT_ID = 207. Further, I'd like to continue that up the line to the root id of 189/0.
I have to use the SUBQUERY w/SUM "Celko Method" by:
select a.id,a.parent_id,a.language_id, a.ctr,
coalesce((select sum(ctr) from ancestor where parent_id >= a.parent_id and id >= a.id and level >= a.level),0)
as total
from ancestor a
but my sums are being done in the wrong order. Have also tried to RANK() the data by PARENT_ID which get's me close but I'm still off.
Any thoughts would be welcome.
July 29, 2011 at 5:59 am
Thanks Joe.
In the short term, I cannot change the DDL based on where we are in our development cycle. I heartily agree that the recursive approach is confusing (one of the reasons I posted this). This DDL is representative and, column names have been changed to protect the innocent :-), not the exact DDL that I am using in my database.
Any idea out there how I may be able to approach this in the short term?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply