CREATE PROCEDURE dbo.[Forum_Group_GetModule] @ModuleID INT
AS
    SELECT  [GroupID],
            [Name],
            [PortalID],
            [ModuleID],
            [SortOrder],
            [CreatedDate],
            [UpdatedDate],
            [CreatedByUser],
            [UpdatedByUser],
            ISNULL(( SELECT COUNT(DISTINCT [ForumID])
                     FROM   dbo.Forum_Forums F
                            INNER JOIN dbo.Forum_Groups G ON F.GroupID = G.GroupID
                     WHERE  ModuleID = @ModuleID
                   ), 0) AS ForumCount,
            ISNULL(( SELECT COUNT(DISTINCT [ForumID])
                     FROM   dbo.Forum_Forums F
                            INNER JOIN dbo.Forum_Groups G ON F.GroupID = G.GroupID
                     WHERE  IsActive = 1
                            AND ModuleID = @ModuleID
                   ), 0) AS ActiveForumCount
    FROM    dbo.Forum_Groups G
    WHERE   [ModuleID] = @ModuleID
    ORDER BY [SortOrder]

GO