使用时 UNION ，从第一个 SELECT 为整个结果集返回。因此，即使您指定“totalbookingswithoutrecurrance”、“totalrecurrance”和“totalbookingswithrecurrance”，您的结果集（给出当前示例）列标题也将是“totalrecurrance”。
我相信你的问题在于你最终的选择：

SELECT  SUM(TotalRecurrance + TotalBookingsWithoutRecurrance) as TotalBookings, TotalBookingsWithRecurrance, RoomID FROM CTE_BOOKINGS Group BY RoomID order by 1 desc

totalbookingswithrecurrance没有被分组，您也没有执行某种聚合操作——因此出现了错误。
保持原样：

SELECT SUM(TotalRecurrance + TotalBookingsWithoutRecurrance) as TotalBookings, TotalBookingsWithRecurrance, RoomID 
FROM CTE_BOOKINGS 
GROUP BY RoomID, TotalBookingsWithRecurrance
ORDER BY 1 DESC

但是，这将添加一个额外的分组级别，我怀疑您不希望这样做。
而且，您的列似乎没有在unioned语句之间对齐，这是必须的——至少是最佳实践（对我来说）。
我无法对此进行测试，但请尝试执行以下操作：

WITH CTE_BOOKINGS ( RoomID, RecurranceType, Recurrance, UnknownCol1, UnknownCol2  ) 
AS (    
    SELECT 
        RM.roomID AS RoomID,
        'TotalBookingsWithoutRecurrance' AS RecurranceType,
        COUNT ( BK.pkBookingID ) AS Recurrance,
        0 AS UnknownCol1,
        0 AS UnknownCol2
    FROM dbo.tblBooking BK WITH (NOLOCK)
    INNER JOIN dbo.tblBookingItem BI WITH (NOLOCK) ON BK.pkBookingID=BI.fkBookingID
    INNER JOIN dbo.tblBookingDateTime BDT WITH (NOLOCK) ON BK.pkBookingID=BDT.fkBookingID
    INNER JOIN Enterprise.tblRooms RM WITH (NOLOCK) ON BI.fkItemID=RM.roomID AND RM.fkResourceId=1
    WHERE BK.deleteBooking=0
        AND BDT.UTC_bookingEnd > GETUTCDATE()
        AND BI.primaryRoom=1
        AND BI.Active=1
        AND NOT EXISTS (select * from tblBookingRecurrance where fkBookingID=BK.pkBookingID AND ACTIVE=1)
        AND RM.roomID=16867
    GROUP BY RM.roomID
    UNION ALL
    SELECT 
        RM.roomID AS RoomID,
        'TotalRecurrance' AS RecurranceType,
        COUNT ( DISTINCT BR.fkRecurranceID ) AS Recurrance,
        0 AS UnknownCol1,
        0 AS UnknownCol2 
    FROM dbo.tblBooking BK WITH (NOLOCK)
    INNER JOIN dbo.tblBookingItem BI WITH (NOLOCK) ON BK.pkBookingID=BI.fkBookingID
    INNER JOIN dbo.tblBookingDateTime BDT WITH (NOLOCK) ON BK.pkBookingID=BDT.fkBookingID
    INNER JOIN Enterprise.tblRooms RM WITH (NOLOCK) ON BI.fkItemID=RM.roomID AND RM.fkResourceId=1
    INNER JOIN tblBookingRecurrance BR WITH (NOLOCK) ON BK.pkBookingID=BR.fkBookingID
    WHERE BK.deleteBooking=0
        AND BDT.UTC_bookingEnd > GETUTCDATE()
        AND BI.primaryRoom=1
        AND BI.Active=1
        AND RM.roomID=16867
        AND BR.active=1
    GROUP BY RM.roomID
    UNION ALL
    SELECT 
        RM.roomID AS RoomID,
        'TotalBookingsWithRecurrance' AS RecurranceType,
        COUNT ( BK.pkBookingID ) AS Recurrance, 
        0 AS UnknownCol1,
        0 AS UnknownCol2
    FROM dbo.tblBooking BK WITH (NOLOCK)
    INNER JOIN dbo.tblBookingItem BI WITH (NOLOCK) ON BK.pkBookingID=BI.fkBookingID
    INNER JOIN dbo.tblBookingDateTime BDT WITH (NOLOCK) ON BK.pkBookingID=BDT.fkBookingID
    INNER JOIN Enterprise.tblRooms RM WITH (NOLOCK) ON BI.fkItemID=RM.roomID AND RM.fkResourceId=1
    WHERE BK.deleteBooking=0
        AND BDT.UTC_bookingEnd > GETUTCDATE()
        AND BI.primaryRoom=1
        AND BI.Active=1
        AND RM.roomID=18841
    GROUP BY RM.roomID
)
SELECT
    SUM ( 
        CASE WHEN RecurranceType IN ( 'TotalRecurrance', 'TotalBookingsWithoutRecurrance' ) THEN Recurrance ELSE 0 END 
    ) AS TotalBookings, 
    SUM ( 
        CASE WHEN RecurranceType = 'TotalBookingsWithRecurrance' THEN Recurrance ELSE 0 END 
    ) AS TotalBookingsWithRecurrance, 
    RoomID 
FROM CTE_BOOKINGS
GROUP BY RoomID
ORDER BY 1 DESC;

你所犯的错误与 UNION . 第三个查询出错。你得先把它修好再把它和另外两个连接起来。
如果按单个字段分组，则该字段将是唯一不带聚合函数的可选字段。这里有一篇文章更深入地解释了这一点。
查看您的查询，您正在筛选特定的roomid，不确定您的确切目标是什么，但在这种情况下，您不需要按roomid分组。