How do you UNION with multiple CTEs?
Sql ServerUnionCommon Table-ExpressionSql 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;
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;