Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query help


Query help

Author
Message
sqlnaive
sqlnaive
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3685 Visits: 2774
I have two tables with folowing sample data:
tabA
Id ParentId
1001 NULL
1002 1001
1003 1001
1004 NULL
1005 NULL

tabB
Id Value
1001 123
1002 25
1003 30
1004 85
1005 218

Now I want a result like this:
Id Value
1001 178 [Aggregate values of 1001+1002+1003]
1004 85
1005 218

As clearly mentioned, 1002 and 1003 should not be there in the result.
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16461 Visits: 13207
If your hierarchy is only one level deep as in the sample data:


WITH(CTE_TableA) AS
(
SELECT JoinKey = COALESCE(ParentID,ID)
FROM tabA
)

SELECT ID = A.JoinKey, Value = SUM(B.Value)
FROM
CTE_TableA A
INNER JOIN
tabB B
ON A.JoinKey = B.ID
GROUP BY A.JoinKey



If you have more levels in your hierarchy, you can use a recursive CTE to flatten the hierarchy and then do the aggregation.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
shashi kant
shashi kant
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 539
Hi SQLNavie,

I achived the solution using Cursor query. Please find below query

Declare @tempTable as table(id int, value Int)

Declare @value as int
DECLARE @CUR AS CURSOR

SET @CUR = CURSOR FOR
Select distinct ID from taba where parentid is null

OPEN @CUR
FETCH NEXT FROM @CUR INTO @value
WHILE @@FETCH_STATUS =0
BEGIN

insert into @temptable (id, value)
select @value, SUM(value) Value from tabb where ID in(Select ID from taba a where parentid = @value or a.ID = @value)
FETCH NEXT FROM @CUR INTO @value


END

CLOSE @CUR
DEALLOCATE @CUR

Select * from @tempTable
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16461 Visits: 13207
shashi kant (7/31/2012)
Hi SQLNavie,

I achived the solution using Cursor query. Please find below query

Declare @tempTable as table(id int, value Int)

Declare @value as int
DECLARE @CUR AS CURSOR

SET @CUR = CURSOR FOR
Select distinct ID from taba where parentid is null

OPEN @CUR
FETCH NEXT FROM @CUR INTO @value
WHILE @@FETCH_STATUS =0
BEGIN

insert into @temptable (id, value)
select @value, SUM(value) Value from tabb where ID in(Select ID from taba a where parentid = @value or a.ID = @value)
FETCH NEXT FROM @CUR INTO @value


END

CLOSE @CUR
DEALLOCATE @CUR

Select * from @tempTable




Please note that cursors have terrible performance, especially when a set-based solution can be used, such as in this case.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
sqlnaive
sqlnaive
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3685 Visits: 2774
Thanks Koen,

With the following configuration:

tabA
Id ParentId
1001 NULL
1002 1001
1003 1001
1004 NULL
1005 NULL

tabB
Id Value
1001 123
1002 25
1003 30
1004 85
1005 218

your query will give results as:
ID Value
1001 369 [123+123+123 which is wrong]
1004 85
1005 218

which means it is repeating the value 123 thrice(once for each iteration while expected result is 123+25+30=178)

I got the correct query. It should be like this:
;with CTE as
(
select a.id as 'P_ID', a.p_id as 'C_ID', b.value
from #temp a
inner join #temp1 b on a.id = b.id
where a.p_id IS NULL
union
select a.p_id as 'P_ID', a.id as 'C_ID', b.value
from #temp a
inner join #temp1 b on a.id = b.id
where a.p_id IS NOT NULL
)
select P_ID, SUM(value)
from CTE
GROUP BY P_ID

It will give the desired result as:
P_ID (No column name)
1001 178 [123+25+30]
1004 85
1005 218

I don't know if we can write it in more efficient way.
sqlnaive
sqlnaive
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3685 Visits: 2774
At the same time, I also strongly agree with you about not using cursors.
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16461 Visits: 13207
You're right, I made an error in my code. Note to self: drink more coffee :-)



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
shashi kant
shashi kant
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 539
Hi Koen,

Your CTE query gives incorrect result for 1001 value.
sqlnaive
sqlnaive
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3685 Visits: 2774
Lolz. Can my query be more optimized ?
shashi kant
shashi kant
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 539
Thanks Koen and Sqlnavie,

CTE way is simple. thanks for updating
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search