+ Reply to Thread
Results 1 to 3 of 3

Thread: SQL - SUM of child columns for a given parent

  1. #1

    SQL - SUM of child columns for a given parent

    I have a forum database that stores forum information in a single column.
    Code:
    | ForumID | ParentForumID | Name | Description | TopicCount | ReplyCount | LastPost |
    Given a ForumID as a parameter I am trying to SUM the TopicCount and ReplyCount for all child entries. I am also trying to return the latest LastPost, which is specified as DATETIME.

    I have used the following code :
    Code:
       WITH CTE (ForumID, ParentForumID)
       AS
       (
           SELECT ForumID AS Descendant, ParentForumID as Ancestor
           FROM forums
           UNION ALL
           SELECT e.Ancestor
           FROM
              CTE as e
              INNER JOIN CTE AS d
              ON Descendant = d.ParentForumID
       )
       SELECT e.Descendant, SUM(TopicCount) AS topics, SUM(ReplyCount) AS replys
       FROM CTE e
       WHERE e.Ancestor = 1

  2. #2
    Junior Member
    Join Date
    Jan 2012
    Posts
    19
    You are so close , the code should look like that :
    Code:
    DECLARE @RootForumID INT
    SET @RootForumID = 5  -- or whatever you want...
    
    ;WITH CTE AS
    (
       -- define the "anchor" query - select the chosen forum
       SELECT 
           ForumID, TopicCount, ReplyCount, LastPost
       FROM 
           dbo.forums
       WHERE
           ForumID = @RootForumID
    
       UNION ALL
    
       -- select the child rows
       SELECT 
           f.ForumID, f.TopicCount, f.ReplyCount, f.LastPost
       FROM 
           dbo.forums f
       INNER JOIN
           CTE on f.ParentForumID = CTE.ForumID
    )
    SELECT 
        SUM(TopicCount) AS topics, 
        SUM(ReplyCount) AS replys,
        MAX(LastPost) AS 'Latest Post' 
    FROM 
        CTE

  3. #3
    Thank you very much for your help .

+ Reply to Thread

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts