Skip to content

SQL Server Error Messages - Msg 467

Error Message

Server: Msg 467, Level 16, State 1, Line 1
GROUP BY, HAVING, or aggregate functions are not allowed in the recursive part of a recursive common table expression '<CTE Name>'.

Causes

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE or CREATE VIEW statement.

A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query. A CTE that references itself is referred to as a recursive common table expression.

The recursive CTE structure must contain at least one anchor member and one recursive member. The general syntax of a recursive common table expression is as follows:

WITH <CTE Name> [ ( [, ...n] ) ]
AS (
    <CTE Query Definition – Anchor Member>
    UNION ALL
    <CTE Query Definition – Recursive Member>
)
SELECT * FROM <CTE Name>

The following items are not allowed in the common table definition query definition of a recursive member:

  • SELECT DISTINCT
  • GROUP BY
  • HAVING
  • Scalar Aggregation
  • TOP
  • LEFT, RIGHT, OUTER JOIN (INNER JOIN is allowed)
  • Subqueries
  • A hint applied to a recursive reference to a CTE inside a CTE_query_definition

For this particular error message, as mentioned above, using a GROUP BY or HAVING in the common table definition query definition of a recursive member will generate this error. To illustrate, the following table structure will be used with the given sample data:

CREATE TABLE [dbo].[Folders] (
    [FolderID]        INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
    [FolderName]      VARCHAR(100),
    [ParentFolderID]  INT
)
GO

ALTER TABLE [dbo].[Folders]
ADD CONSTRAINT [FK_Folders_Folders] FOREIGN KEY ( [ParentFolderID] )
    REFERENCES [dbo].[Folders] ( [FolderID] )
GO

CREATE TABLE [dbo].[Files] (
    [FileID]          INT NOT NULL PRIMARY KEY IDENTITY (1, 1),
    [FileName]        VARCHAR(100),
    [FolderID]        INT
)
GO

ALTER TABLE [dbo].[Files]
ADD CONSTRAINT [FK_Files_Folders] FOREIGN KEY ( [FolderID] )
    REFERENCES [dbo].[Folders] ( [FolderID] )
GO

INSERT INTO [dbo].[Folders] ( [FolderName], [ParentFolderID] )
VALUES ( 'C:', NULL )

INSERT INTO [dbo].[Folders] ( [FolderName], [ParentFolderID] )
VALUES ( 'Program Files', 1 )

INSERT INTO [dbo].[Folders] ( [FolderName], [ParentFolderID] )
VALUES ( 'Windows', 1 )

INSERT INTO [dbo].[Folders] ( [FolderName], [ParentFolderID] )
VALUES ( 'Microsoft SQL Server', 2 )

INSERT INTO [dbo].[Folders] ( [FolderName], [ParentFolderID] )
VALUES ( 'system32', 3 )
GO

INSERT INTO [dbo].[Files] ( [FileName], [FolderID] )
VALUES ( 'install.ini', 1 )

INSERT INTO [dbo].[Files] ( [FileName], [FolderID] )
VALUES ( 'web.config', 2 )

INSERT INTO [dbo].[Files] ( [FileName], [FolderID] )
VALUES ( 'notepad.exe', 3 )

INSERT INTO [dbo].[Files] ( [FileName], [FolderID] )
VALUES ( 'sql.config', 4 )

INSERT INTO [dbo].[Files] ( [FileName], [FolderID] )
VALUES ( 'error.log', 4 )

INSERT INTO [dbo].[Files] ( [FileName], [FolderID] )
VALUES ( 'readme.txt', 5 )

INSERT INTO [dbo].[Files] ( [FileName], [FolderID] )
VALUES ( 'setup.ini', 5 )

INSERT INTO [dbo].[Files] ( [FileName], [FolderID] )
VALUES ( 'java.exe', 5 )

The following recursive common-table expression (CTE) lists all the folders displaying the full path of each folder:

WITH [FolderHierarchy] AS (
    SELECT CAST([FolderName] AS VARCHAR(MAX)) AS [FolderName], [FolderID]
    FROM [dbo].[Folders]
    WHERE [ParentFolderID] IS NULL
    UNION ALL
    SELECT CAST(B.[FolderName] + '\' + A.[FolderName] AS VARCHAR(MAX)) AS [FolderName],
           A.[FolderID]
    FROM [dbo].[Folders] A INNER JOIN [FolderHierarchy] B
      ON A.[ParentFolderID] = B.[FolderID]
)
SELECT *
FROM [FolderHierarchy]
ORDER BY [FolderName]
GO

The output of this common-table expression is as follows using the sample data shown earlier:

FolderName                             FolderID
-------------------------------------- -----
C:                                     1
C:\Program Files                       2
C:\Program Files\Microsoft SQL Server  4
C:\Windows                             3
C:\Windows\system32                    5

The following recursive common-table expression (CTE) tries to return all folders defined in the [dbo].[Folders] table together with the number of files on each folder.

WITH [FolderHierarchy] AS (
    SELECT CAST(A.[FolderName] AS VARCHAR(MAX)) AS [FolderName], A.[FolderID],
           COUNT(B.[FileName]) AS [FileCount]
    FROM [dbo].[Folders] A INNER JOIN [dbo].[Files] B
                                   ON A.[FolderID] = B.[FolderID] AND
                                      A.[ParentFolderID] IS NULL
    GROUP BY CAST(A.[FolderName] AS VARCHAR(MAX)), A.[FolderID]
    UNION ALL
    SELECT CAST(B.[FolderName] + '\' + A.[FolderName] AS VARCHAR(MAX)) AS [FolderName],
           A.[FolderID], COUNT(C.[FileName]) AS [FileCount]
    FROM [dbo].[Folders] A INNER JOIN [FolderHierarchy] B
                                   ON A.[ParentFolderID] = B.[FolderID]
                           INNER JOIN [dbo].[Files] C
                                   ON A.[FolderID] = C.[FolderID]
    GROUP BY CAST(B.[FolderName] + '\' + A.[FolderName] AS VARCHAR(MAX)), A.[FolderID]
)
SELECT *
FROM [FolderHierarchy]
ORDER BY [FolderName]
GO

The expected output of the CTE is as follows:

FolderName                             FileCount
-------------------------------------- -----------
C:                                     1
C:\Program Files                       1
C:\Program Files\Microsoft SQL Server  2
C:\Windows                             1
C:\Windows\system32                    3

But since the recursive CTE uses the GROUP BY clause in the recursive part of the recursive common table expression, the following error message is raised:

Msg 467, Level 16, State 1, Line 1
GROUP BY, HAVING, or aggregate functions are not allowed in the recursive part of a recursive common table expression 'FolderHierarchy'.

Solution / Work Around

There are a couple of ways of working around this error message and still be able to accomplish the result needed from a recursive CTE. The first option is with the use of a function that will accept a folder ID and return the number of files within that folder. Here’s how the user-defined function will look like:

CREATE FUNCTION [dbo].[ufn_GetFileCount] ( @FolderID INT )
RETURNS INT
AS
BEGIN
    RETURN (SELECT COUNT(*) FROM [dbo].[Files]
            WHERE [FolderID] = @FolderID)
END
GO

And here’s how the recursive CTE will look like using the user-defined function:

WITH [FolderHierarchy] AS (
    SELECT CAST(A.[FolderName] AS VARCHAR(MAX)) AS [FolderName], A.[FolderID],
           [dbo].[ufn_GetFileCount] ( A.[FolderID] ) AS [FileCount]
    FROM [dbo].[Folders] A INNER JOIN [dbo].[Files] B
                                   ON A.[FolderID] = B.[FolderID] AND
                                      A.[ParentFolderID] IS NULL
    UNION ALL
    SELECT CAST(B.[FolderName] + '\' + A.[FolderName] AS VARCHAR(MAX)) AS [FolderName],
           A.[FolderID], [dbo].[ufn_GetFileCount] ( A.[FolderID] ) AS [FileCount]
    FROM [dbo].[Folders] A INNER JOIN [FolderHierarchy] B
                                   ON A.[ParentFolderID] = B.[FolderID]
)
SELECT [FolderName], [FileCount]
FROM [FolderHierarchy]
ORDER BY [FolderName]
GO

Here’s the output of this recursive CTE, which is the desired output:

FolderName                             FileCount
-------------------------------------- -----------
C:                                     1
C:\Program Files                       1
C:\Program Files\Microsoft SQL Server  2
C:\Windows                             1
C:\Windows\system32                    3

The second option in working around this error message is with the use of a view. The view will return each folder together with the number of files within each folder. Here’s the definition of the view:

CREATE VIEW [dbo].[FolderFileCount]
AS
SELECT A.[FolderID], A.[FolderName], COUNT(B.[FileName]) AS [FileCount]
FROM [dbo].[Folders] A LEFT OUTER JOIN [dbo].[Files] B
                                    ON A.[FolderID] = B.[FolderID]
GROUP BY A.[FolderID], A.[FolderName]
GO

Here’s an updated version of the recursive CTE which uses the newly defined view:

WITH [FolderHierarchy] AS (
    SELECT CAST(A.[FolderName] AS VARCHAR(MAX)) AS [FolderName], A.[FolderID],
           B.[FileCount]
    FROM [dbo].[Folders] A INNER JOIN [dbo].[FolderFileCount] B
                                   ON A.[FolderID] = B.[FolderID] AND
                                      A.[ParentFolderID] IS NULL
    UNION ALL
    SELECT CAST(B.[FolderName] + '\' + A.[FolderName] AS VARCHAR(MAX)) AS [FolderName],
           A.[FolderID], C.[FileCount]
    FROM [dbo].[Folders] A INNER JOIN [FolderHierarchy] B
                                   ON A.[ParentFolderID] = B.[FolderID]
                           INNER JOIN [dbo].[FolderFileCount] C
                                   ON A.[FolderID] = C.[FolderID]
)
SELECT [FolderName], [FileCount]
FROM [FolderHierarchy]
ORDER BY [FolderName]
GO