July 30, 2012 at 11:48 pm
I have two tables with folowing sample data:
tabA
IdParentId
1001NULL
10021001
10031001
1004NULL
1005NULL
tabB
IdValue
1001123
100225
100330
100485
1005218
Now I want a result like this:
IdValue
1001178[Aggregate values of 1001+1002+1003]
100485
1005218
As clearly mentioned, 1002 and 1003 should not be there in the result.
July 31, 2012 at 12:37 am
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 31, 2012 at 12:59 am
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
July 31, 2012 at 1:00 am
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 31, 2012 at 1:04 am
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:
IDValue
1001369 [123+123+123 which is wrong]
100485
1005218
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 #tempa
inner join #temp1bona.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 #tempa
inner join #temp1bona.id = b.id
where a.p_id IS NOT NULL
)
selectP_ID, SUM(value)
fromCTE
GROUP BY P_ID
It will give the desired result as:
P_ID(No column name)
1001178 [123+25+30]
100485
1005218
I don't know if we can write it in more efficient way.
July 31, 2012 at 1:08 am
At the same time, I also strongly agree with you about not using cursors.
July 31, 2012 at 1:10 am
You're right, I made an error in my code. Note to self: drink more coffee 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 31, 2012 at 1:11 am
Hi Koen,
Your CTE query gives incorrect result for 1001 value.
July 31, 2012 at 1:11 am
Lolz. Can my query be more optimized ?
July 31, 2012 at 1:21 am
Thanks Koen and Sqlnavie,
CTE way is simple. thanks for updating
July 31, 2012 at 1:22 am
sqlnaive (7/31/2012)
Lolz. Can my query be more optimized ?
You can try this:
WITH CTE_TableA AS
(
SELECT AggregateKey = COALESCE(ParentID,ID), JoinKey = ID
FROM tabA
)
SELECT ID = A.AggregateKey, Value = SUM(B.Value)
FROM
CTE_TableA A
INNER JOIN
tabB B
ON A.JoinKey = B.ID
GROUP BY A.AggregateKey;
(this time I tested it with the sample data and it gives the correct result :D)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 31, 2012 at 2:01 am
Edit: Wrong thread for this post.
July 31, 2012 at 5:45 am
Here are two different methods for accomplishing the same goal. One uses hierarchyid, while the other uses a simple LEFT join. Excuse my bad grammar on my lack of coffee. 😛
DECLARE @tabA TABLE(Id INT, ParentId INT)
INSERT @tabA
SELECT 1001,NULL
UNION ALL
SELECT 1002,1001
UNION ALL
SELECT 1003,1001
UNION ALL
SELECT 1004,NULL
UNION ALL
SELECT 1005,NULL
DECLARE @tabB TABLE(Id INT, Value INT)
INSERT @tabb
SELECT 1001,123
UNION ALL
SELECT 1002,25
UNION ALL
SELECT 1003,30
UNION ALL
SELECT 1004,85
UNION ALL
SELECT 1005,218
IF OBJECT_ID('tempdb..#results') IS NOT NULL
DROP TABLE #results
CREATE TABLE #results(
id INT,
parentid INT,
level INT,
hid hierarchyid
)
--====================================================
--== Build the hierarchy(hid) using a recursive CTE that will handle multiple
--== levels. When this is completed you can converse the tree and find
--== all descendants. HierarchyId has to be in the format of /(int)/(int)/.
--====================================================
;WITH cte
AS (
SELECT id,ParentId,level = 1,hid = CAST('/' + CAST(DENSE_RANK() OVER (ORDER BY id) AS VARCHAR(9)) + '/' AS VARCHAR(MAX))
FROM @tabA
WHERE ParentId IS NULL
UNION ALL
SELECT t.Id,t.ParentId,level + 1,CAST(hid + CAST(DENSE_RANK() OVER (ORDER BY t.id) AS VARCHAR(9)) + '/' AS VARCHAR(MAX))
FROM cte c
INNER JOIN @tabA t ON c.Id = t.ParentId
)
INSERT #results
SELECT * FROM cte
--====================================================
--== A self reference to the #results table gives the ability to find
--== all descendants. The results of the method IsDecendantOf = 1 means
--== that it is a valid descendant. A descendant can also be its own value.
--====================================================
SELECT p.id,total = SUM(v.value)
FROM #results c INNER JOIN #results p ON c.hid.IsDescendantOf(p.hid) = 1 AND p.level = 1
LEFT JOIN @tabB v ON c.id = v.Id
GROUP BY p.id
--====================================================
-- Here is a simple LEFT JOIN between the two tables.
--====================================================
SELECT id = ISNULL(t.ParentId,t.Id),total = SUM(v.value) FROM @tabA t LEFT JOIN @tabB v ON t.Id = v.Id
GROUP BY ISNULL(t.ParentId,t.Id)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply