How do you UNION with multiple CTEs?

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_DataUnionSelect Count(Distinct Client_No)From dbo.Decision_DataUnionSelect Count(*)From ClientsUnionSelect 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)

You can do it like this:

WITH    [Clients]          AS ( SELECT   Client_No               FROM     dbo.Decision_Data               GROUP BY Client_No               HAVING   COUNT(*) = 1             ),        [Clients2]          AS ( SELECT   Client_No               FROM     dbo.Decision_Data               GROUP BY Client_No               HAVING   COUNT(*) = 1             )SELECT  COUNT(*)FROM    ClientsUNIONSELECT  COUNT(*)FROM    Clients2;