InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 20:48:55 GMT20http://www.sqlservercentral.com/Forums/Topic1404129-391-1.aspxThanks So much Chris and it works. I recognize the deep thought you might have put in it. Thanks for your time...Tue, 08 Jan 2013 07:03:32 GMTS_Kumar_Shttp://www.sqlservercentral.com/Forums/Topic1404129-391-1.aspx[code="sql"];WITH CTE AS( SELECT forumId,ParentId,HitNumber, Seq = CAST(','+CAST(forumId AS VARCHAR(2)) AS VARCHAR(20)) FROM #tab1 WHERE ParentId IS NULL UNION ALL SELECT DD2.forumId, DD2.ParentId, DD2.HitNumber, Seq = CAST(CTE.Seq + ',' + CAST(DD2.forumId AS VARCHAR(2)) AS VARCHAR(20)) FROM #tab1 AS DD2 INNER JOIN CTE ON CTE.ForumId = DD2.parentId)SELECT c.forumId, c.ParentId, x.TotalHitNumber FROM CTE cCROSS APPLY ( SELECT TotalHitNumber = SUM(HitNumber) FROM CTE WHERE Seq+',' LIKE '%,'+CAST(c.forumId AS VARCHAR(2))+',%') xORDER BY c.forumId [/code]Tue, 08 Jan 2013 06:41:42 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1404129-391-1.aspxCorrect Sir...Tue, 08 Jan 2013 05:14:11 GMTS_Kumar_Shttp://www.sqlservercentral.com/Forums/Topic1404129-391-1.aspxSo for each forum ID, you want the number of hits, including the number of hits for any child forums.4 885 5716 14639 203811 12815 5410 10658 4993 118413 789Tue, 08 Jan 2013 05:08:44 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1404129-391-1.aspxProblem is :I have hard coded the query for ForumId=9. But I need the SUM of hits for All ForumIds individually. Something like:ForumId SumOfHitCount9 20383 1184.................................................Tue, 08 Jan 2013 04:36:13 GMTS_Kumar_Shttp://www.sqlservercentral.com/Forums/Topic1404129-391-1.aspx[quote][b]S_Kumar_S (1/8/2013)[/b][hr]Sorry for confusion. I am trying to remove a user defined function already built, not the sum function[/quote]Ok what is the problem you are having with the code, it seems to work for me.Tue, 08 Jan 2013 04:26:28 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1404129-391-1.aspxSorry for confusion. I am trying to remove a user defined function already built, not the sum functionTue, 08 Jan 2013 04:25:26 GMTS_Kumar_Shttp://www.sqlservercentral.com/Forums/Topic1404129-391-1.aspxWhat function are you trying to remove?The SUM?[strike]I would of thought that Azure would come with the bulit in aggregating functions as standard.[/strike][url]http://msdn.microsoft.com/en-us/library/windowsazure/ee336248.aspx#aggregate[/url]Azure does use the standard aggregating functionsTue, 08 Jan 2013 04:23:43 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1404129-391-1.aspxHi AllFirst schema:create table tab1 (forumId int Primary key,ParentId int,HitNumber int)insert into tab1select 3, 9, 119 UNION ALLselect 4, NULL, 88 UNION ALLselect 5, NULL, 72 UNION ALLselect 6 ,NULL ,1463 UNION ALLselect 8 ,5 ,499 UNION ALLselect 9 ,NULL ,65 UNION ALLselect 10, 3, 1065 UNION ALLselect 11, NULL, 128 UNION ALLselect 13, 9 ,789 UNION ALLselect 15 ,NULL, 54;WITH CTE AS( SELECT forumId,ParentId,HitNumber FROM [tab1] WHERE ForumID=9 UNION ALL SELECT DD2.forumId,DD2.ParentId,DD2.HitNumber FROM tab1 as DD2 INNER JOIN CTE ON DD2.parentId=CTE.ForumId)SELECT SUM(HitNumber) FROM CTE --select * from CTEIn Above table , ForumId 9 is Parent to ForumId 3 and 13. 3 Is parent to 10. Now as you can see, I have hard coded the query for ForumId=9. But I need the SUM of hits for All ForumIds. So I need SUM of Hits for ForumID 9,3,13,10.I don't have to use a funtion. In fact the whole exercise is for removing function for Azure.Help pls.Tue, 08 Jan 2013 04:15:59 GMTS_Kumar_S