SQL Recursive Count

I have two tables I am joining with the following structure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ContentDivider](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ParentId] [int] NULL,
    [Name] [nvarchar](128) NOT NULL,
 CONSTRAINT [PK_ContentDivider] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[CustomPage]    Script Date: 23-03-2020 17:46:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CustomPage](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ContentDividerId] [int] NOT NULL,
    [Name] [nvarchar](128) NOT NULL,
 CONSTRAINT [PK_CustomPage] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

A ContentDivider can have n ContentDividers as Children and can have m CustomPages as children as well. I want a View that counts the Display the current CustomDivider and the COunt for all the CustomPages as Children of the current ContentDivider.

My Test data:

SET IDENTITY_INSERT [dbo].[ContentDivider] ON 
GO
INSERT [dbo].[ContentDivider] ([Id], [ParentId], [Name]) VALUES (1, NULL, N'TopLevel1')
INSERT [dbo].[ContentDivider] ([Id], [ParentId], [Name]) VALUES (2, NULL, N'TopLevel2')
INSERT [dbo].[ContentDivider] ([Id], [ParentId], [Name]) VALUES (3, NULL, N'TopLevel3')
INSERT [dbo].[ContentDivider] ([Id], [ParentId], [Name]) VALUES (4, 1, N'SecondLevel1')
INSERT [dbo].[ContentDivider] ([Id], [ParentId], [Name]) VALUES (5, 1, N'SecondLevel2')
INSERT [dbo].[ContentDivider] ([Id], [ParentId], [Name]) VALUES (6, 1, N'SecondLevel3')
INSERT [dbo].[ContentDivider] ([Id], [ParentId], [Name]) VALUES (7, 4, N'ThirdLevel1')
INSERT [dbo].[ContentDivider] ([Id], [ParentId], [Name]) VALUES (8, 4, N'ThirdLevel2')
GO
SET IDENTITY_INSERT [dbo].[ContentDivider] OFF
GO
SET IDENTITY_INSERT [dbo].[CustomPage] ON 
GO
INSERT [dbo].[CustomPage] ([Id], [ContentDividerId], [Name]) VALUES (1, 1, N'Level1_1')
INSERT [dbo].[CustomPage] ([Id], [ContentDividerId], [Name]) VALUES (2, 1, N'Level1_2')
INSERT [dbo].[CustomPage] ([Id], [ContentDividerId], [Name]) VALUES (3, 2, N'Level1_3')
INSERT [dbo].[CustomPage] ([Id], [ContentDividerId], [Name]) VALUES (4, 2, N'Level1_4')
INSERT [dbo].[CustomPage] ([Id], [ContentDividerId], [Name]) VALUES (5, 4, N'Level1_5')
INSERT [dbo].[CustomPage] ([Id], [ContentDividerId], [Name]) VALUES (6, 5, N'Level1_6')
INSERT [dbo].[CustomPage] ([Id], [ContentDividerId], [Name]) VALUES (7, 7, N'Level1_7')
INSERT [dbo].[CustomPage] ([Id], [ContentDividerId], [Name]) VALUES (8, 8, N'Level1_8')
GO
SET IDENTITY_INSERT [dbo].[CustomPage] OFF
GO

And the View I want to extend:

SELECT        dbo.ContentDivider.ParentId, dbo.ContentDivider.Name, dbo.ContentDivider.Id, COUNT(DISTINCT dbo.CustomPage.Id) AS CustomPageCount
FROM            dbo.ContentDivider LEFT OUTER JOIN
                         dbo.CustomPage ON dbo.ContentDivider.Id = dbo.CustomPage.ContentDividerId
GROUP BY dbo.ContentDivider.ParentId, dbo.ContentDivider.Name, dbo.ContentDivider.Id

As for now the view counts the custompages directly underneath the contentdivider. I would like all the CustomPages as children counted. Any suggestions?

The respected result would be:

View

Answer

this sounds like a perfect situation for recursive cte 😉 So, if I understood correctly, your expected result would be Toplevel1 with 6 pages and Toplevel 2 with 2 pages since all the other levels are somewhere beneath these two mentioned levels?

The cte might look something like this (maybe you habe to include the max recursion option):

WITH cte AS(
SELECT 1 lvl, ID AS ParentID, ID, Name
  FROM dbo.ContentDivider cd
  WHERE ParentId IS NULL
UNION ALL
SELECT c.lvl+1 AS lvl, c.ParentID, cd.ID, cd.Name
  FROM dbo.ContentDivider cd
  INNER JOIN cte c ON cd.ParentID = c.ID
)
SELECT c.ParentID, cd.Name, COUNT(DISTINCT cp.Id) AS CustomPageCount
  FROM cte c
  JOIN dbo.ContentDivider cd ON cd.ID = c.ParentID
  LEFT OUTER JOIN dbo.CustomPage cp ON cp.ContentDividerId = c.id
  GROUP BY c.ParentId, cd.Name

This leads to all pages being assigned to its top level.

See fiddle for details: http://sqlfiddle.com/#!18/f1a44/28/1

Edit: Since you need the details down to DividerID, I extended my example in the fiddle. First of all, I fetch the PageCount per ID in one cte and additionally the PageCount aggregated on level (ParentID and all its IDs) – this done you don’t need the count and grouping in the following ctes. In my query I then check, if my current rows ID is a top-level of any kind and assign the corresponding PageCount to this row.

WITH cteCnt AS(
  SELECT cd.ID, COUNT(DISTINCT cp.Id) AS CustomPageCount
    FROM dbo.ContentDivider cd
    LEFT OUTER JOIN dbo.CustomPage cp ON cp.ContentDividerId = cd.id
    GROUP BY cd.ID
),
cteTop AS(
  SELECT cd.ID, COUNT(DISTINCT cp.Id) AS CustomPageCount
    FROM dbo.ContentDivider cd
    LEFT OUTER JOIN dbo.CustomPage cp ON cp.ContentDividerId = cd.id
    GROUP BY cd.ID
  UNION ALL
  SELECT cd.ParentID, COUNT(DISTINCT cp.Id) AS CustomPageCount
    FROM dbo.ContentDivider cd
    LEFT OUTER JOIN dbo.CustomPage cp ON cp.ContentDividerId = cd.id
    WHERE cd.ParentID IS NOT NULL 
    GROUP BY cd.ParentID
),
cteTopSum AS(
SELECT ID, SUM(CustomPageCount) AS CustomPageCount
  FROM ctetop
  GROUP BY ID
),  
cte AS(
SELECT 1 lvl, cd.ID AS ParentID, cd.ID AS ParentIDx, cd.ID, cd.Name, cnt.CustomPageCount
  FROM dbo.ContentDivider cd
  INNER JOIN cteCnt cnt ON cnt.ID = cd.ID
  WHERE ParentId IS NULL
UNION ALL
SELECT c.lvl+1 AS lvl, c.ParentID, cd.ParentID AS ParentIDx, cd.ID, cd.Name, cnt.CustomPageCount
  FROM dbo.ContentDivider cd
  INNER JOIN cteCnt cnt ON cnt.ID = cd.ID
  INNER JOIN cte c ON cd.ParentID = c.ID
),
cteOut AS(
SELECT *
  ,SUM(CustomPageCount) OVER (PARTITION BY ParentID) x
  ,SUM(CustomPageCount) OVER (PARTITION BY ParentIDx) y
  FROM cte c
)
SELECT CASE WHEN co.ParentIDx = co.ID THEN NULL ELSE co.ParentIDx END AS ParentID, co.ID, co.Name, CASE WHEN co.ID = co.ParentID THEN co.X ELSE ts.CustomPageCount END CustomPageCount
  FROM cteOut co
  LEFT JOIN cteTopSum ts ON ts.ID = co.ID
  ORDER BY 1, 2

See new fiddle for details: http://sqlfiddle.com/#!18/f1a44/185/1

I’m mot sure, if there is a prettier / nicer way to solve this, but seemingly this seems to solve the problem. However, I did NOT check if it works if any number of sublevels or whatsoever – if you find any issues, feel free to comment.