Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Query help Expand / Collapse
Author
Message
Posted Monday, July 30, 2012 11:48 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, October 10, 2014 7:07 AM
Points: 3,545, Visits: 2,654
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.
Post #1337678
Posted Tuesday, July 31, 2012 12:37 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:42 AM
Points: 13,295, Visits: 11,086
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1337684
Posted Tuesday, July 31, 2012 12:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 13, 2014 12:12 AM
Points: 162, Visits: 521
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


Post #1337694
Posted Tuesday, July 31, 2012 1:00 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:42 AM
Points: 13,295, Visits: 11,086
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1337695
Posted Tuesday, July 31, 2012 1:04 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, October 10, 2014 7:07 AM
Points: 3,545, Visits: 2,654
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.
Post #1337696
Posted Tuesday, July 31, 2012 1:08 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, October 10, 2014 7:07 AM
Points: 3,545, Visits: 2,654
At the same time, I also strongly agree with you about not using cursors.
Post #1337697
Posted Tuesday, July 31, 2012 1:10 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:42 AM
Points: 13,295, Visits: 11,086
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1337698
Posted Tuesday, July 31, 2012 1:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 13, 2014 12:12 AM
Points: 162, Visits: 521
Hi Koen,

Your CTE query gives incorrect result for 1001 value.
Post #1337700
Posted Tuesday, July 31, 2012 1:11 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, October 10, 2014 7:07 AM
Points: 3,545, Visits: 2,654
Lolz. Can my query be more optimized ?
Post #1337701
Posted Tuesday, July 31, 2012 1:21 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 13, 2014 12:12 AM
Points: 162, Visits: 521
Thanks Koen and Sqlnavie,

CTE way is simple. thanks for updating
Post #1337704
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse