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

score 130 · Accepted Answer · edited Apr 10 '16 at 08:26

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)

@SteveC - In short, you need to declare all the CTEs you want to use in your final expression first and then use them in your union query. — Thomas, Jul 18 '12 at 13:28
Thanks. The use of a comma to declare multiple CTE's was what i was missing. — Dan Walters, Sep 10 '19 at 13:35

DaveShaw · Answer 2 · 2012-07-18T13:29:30.547

17

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;

edited Jul 18 '12 at 13:29

answered Jul 18 '12 at 13:18

DaveShaw

52,123
16
112
141

My bad ... I want to UNION multiple CTE's – SteveC Jul 18 '12 at 13:24
This wont work as you are using the Clients CTE after declaring Clients2 CTE.Its out of referrence – AnandPhadke Jul 18 '12 at 13:32
1

@AnandPhadke - This works fine, I cannot understand why you say it won't work – DaveShaw Jul 18 '12 at 13:36
@DaveShaw -- Yeah I was thinking of having some select statements between CTEs.If we have any sql statements between CTE's then it wont work.This case yes it will work. – AnandPhadke Jul 18 '12 at 13:41

How do you UNION with multiple CTEs?

2 Answers2

Linked

Related