How do you UNION with multiple CTEs?

Sql ServerUnionCommon Table-Expression

Sql Server Problem Overview


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

Sql Server Solutions


Solution 1 - Sql Server

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)

Solution 2 - Sql Server

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    Clients
UNION
SELECT  COUNT(*)
FROM    Clients2;

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionSteveCView Question on Stackoverflow
Solution 1 - Sql ServerThomasView Answer on Stackoverflow
Solution 2 - Sql ServerDaveShawView Answer on Stackoverflow