Selecting from two different Common Table Expressions (CTEs)-CodePudding

I have the following two common table expressions:

WITH cte_1 AS (

    SELECT
    
    u.id AS users,
    count(o.id) AS order_count

    FROM 
        users u
    
    LEFT JOIN
        orders o
    
    ON  u.id = o.user_id

    WHERE 
        u.created_at BETWEEN '2019-07-01 00:00:00.000000' AND '2019-09-30 23:59:59.999999' 

    GROUP BY u.id

), 
cte_2 AS (
    SELECT
    
    u.id AS users,
    count(o.id) AS order_count

    FROM 
        users u
    
    LEFT JOIN
        orders o
    
    ON  u.id = o.user_id

    WHERE 
        u.created_at BETWEEN '2019-07-01 00:00:00.000000' AND '2019-09-30 23:59:59.999999' 

    GROUP BY u.id

    HAVING COUNT(o.id) > 0    
)

Notice that both CTEs are exactly the same (with the exception of HAVING COUNT(o.id) > 0 clause in cte_2).

If I independently run the query inside of cte_1, I get a value of 200. If I independently run the query inside of cte_2, I get a value of 75. I'm trying to run a single query using these CTEs to get the following counts:

foo   bar
200   75

I tried the following (which is syntactically incorrect):

WITH cte_1 AS (

    SELECT
    
    u.id AS users,
    count(o.id) AS order_count

    FROM 
        users u
    
    LEFT JOIN
        orders o
    
    ON  u.id = o.user_id

    WHERE 
        u.created_at BETWEEN '2019-07-01 00:00:00.000000' AND '2019-09-30 23:59:59.999999' 

    GROUP BY u.id

), 
cte_2 AS (
    SELECT
    
    u.id AS users,
    count(o.id) AS order_count

    FROM 
        users u
    
    LEFT JOIN
        orders o
    
    ON  u.id = o.user_id

    WHERE 
        u.created_at BETWEEN '2019-07-01 00:00:00.000000' AND '2019-09-30 23:59:59.999999' 

    GROUP BY u.id

    HAVING COUNT(o.id) > 0    
)


SELECT count(users) as foo
FROM cte_1

SELECT count(order_count) as bar
from cte2

Any assistance you can give this SQL newbie would be greatly appreciated! Thanks!

CodePudding user response：

with summary as (
    SELECT u.id AS userid, count(o.id) AS order_count
    FROM users u LEFT JOIN orders o ON u.id = o.user_id
    WHERE u.created_at BETWEEN '2019-07-01 00:00:00.000000'
                           AND '2019-09-30 23:59:59.999999' 
    GROUP BY u.id
)
select count(userid), count(case when order_count > 0 then 1 end)
from summary;

CodePudding user response：

WITH
    cte as (
        SELECT
            u.id AS userid,
            count(o.id) AS order_count
        FROM 
            users u
            LEFT JOIN
            orders o ON u.id = o.user_id
        WHERE 
            u.created_at BETWEEN '2019-07-01 00:00:00.000000' AND '2019-09-30 23:59:59.999999' 
        GROUP BY
            u.id
    )
SELECT
    count(userid) as foo,
    sum(case when order_count > 0 then 1 else 0 end) as bar
FROM
    cte

CodePudding user response：

Assuming that you must use these CTEs, and this is not a XY problem, you could use a CROSS JOIN to join these results like so:

SELECT foo_q.foo, bar_q.bar
FROM 
(SELECT count(users) as foo
FROM cte_1) as foo_q
CROSS JOIN
(SELECT count(order_count) as bar
FROM cte_2) as bar_q

CodePudding user response：

Both shawnt00 and Kurt have fine answers. There's another slightly simpler approach to obtain that result.

While it's not a direct response to the question about using those CTE terms, it might be interesting to the new SQL user, and it's standard SQL:

Test case with PG

SELECT COUNT(DISTINCT u.id) AS foo
     , COUNT(DISTINCT u.id) FILTER (WHERE o.id IS NOT NULL) AS bar
  FROM users u LEFT JOIN orders o ON u.id = o.user_id
 WHERE u.created_at BETWEEN '2019-07-01 00:00:00.000000'
                        AND '2019-09-30 23:59:59.999999'
;

CodePudding user response：

WITH ...

SELECT (
  SELECT count(users) as foo
  FROM cte_1
) foo,

(
  SELECT count(order_count) as bar
  from cte2
) bar