How do you UNION with multiple CTEs?

Viewed 101495

How do you use UNION with multiple Common Table Expressions?

I'm trying to put together some summary numbers but no matter where I put the ;, I always get an error

SELECT  COUNT(*)
FROM    dbo.Decision_Data
UNION
SELECT  COUNT(DISTINCT Client_No)
FROM    dbo.Decision_Data
UNION
WITH    [Clients]
          AS ( SELECT   Client_No
               FROM     dbo.Decision_Data
               GROUP BY Client_No
               HAVING   COUNT(*) = 1
             )
    SELECT  COUNT(*) AS [Clients Single Record CTE]
    FROM    Clients;

I appreciate in the example above I can move the single CTE to the beginning, but I have a number of CTEs I'd like to UNION

2 Answers
Related