How do you UNION with multiple CTEs?

Question

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

Answer 1

If you are trying to union multiple CTEs, then you need to declare the CTEs first and then use them:

With Clients As     (     Select Client_No     From dbo.Decision_Data     Group By Client_No     Having Count(*) = 1     )     , CTE2 As     (     Select Client_No     From dbo.Decision_Data     Group By Client_No     Having Count(*) = 2     ) Select Count(*) From Decision_Data Union Select Count(Distinct Client_No) From dbo.Decision_Data Union Select Count(*) From Clients Union Select Count(*) From CTE2; 

You can even use one CTE from another:

With Clients As         (         Select Client_No         From dbo.Decision_Data         Group By Client_No         Having Count(*) = 1         )         , CTE2FromClients As         (         Select Client_No         From Clients         )     Select Count(*)     From Decision_Data     Union     Select Count(Distinct Client_No)     From dbo.Decision_Data     Union     Select Count(*)     From Clients     Union     Select Count(*)     From CTE2FromClients; 

WITH common_table_expression (Transact-SQL)