GROUP BY, HAVING, or aggregate functions are not allowed in the recursive part of a recursive common table expression ‘%.*ls’.

Error Message:
Msg 467, Level 16, State 1, Line 2
GROUP BY, HAVING, or aggregate functions are not allowed in the recursive part of a recursive common table expression ‘%.*ls’.

Severity level:
16.

Description:
This error message appears when you try to use a GROUP BY or HAVING clause or a subquery in the recursive part of a common table expression.

Consequences:
The T-SQL statement can be parsed, but causes the error at runtime.

Resolution:
Error of the Severity level 16 are generated by the user and are corrigible by the user. The statement can not be run this way. You can not use a GROUP BY or HAVING clause or a subquery in the recursive part of a common table expression.

Versions:
This error message was introduced with SQL Server 2005.

Example(s):
IF OBJECT_ID(‘dbo.t’) IS NOT NULL
    DROP TABLE dbo.t
GO

CREATE TABLE dbo.t
(
    id INT,
    reportToID INT NULL,
    salary int
);
GO

INSERT INTO dbo.t
SELECT 1, NULL, 1000 UNION ALL
SELECT 2, 1, 100 UNION ALL
SELECT 3, 1, 100 UNION ALL
SELECT 4, 2, 100;
GO

WITH MyCTE (id, salary, reportToID, Level)
AS
(
SELECT
    t1.ID, t1.reportToID, t1.salary, 0 AS Level
FROM
    dbo.t AS t1
WHERE
    reportToID IS NULL
UNION ALL
SELECT
    t1.ID, SUM(t1.salary), MIN(t1.reportToID), Level
FROM
    dbo.t AS t1
    JOIN
    MyCTE AS t2 ON t1.reportToID = t2.ID
GROUP BY
    t1.ID, Level
)

SELECT
    *
FROM
    MyCTE;
GO

Remarks:
In the above example we try to use a GROUP BY clause in the recursive part of a common table expression. This raises the error.