Combining multiple cte

Question

Is it possible to combine a result of two cte to another cte. I wrote a query combining two cte. The result gave a three column data in which I want to group the third column and averaging the second column. The second column resulted from a case sum statement.

Answer 1

If you are asking whether you can re-use CTEs after they have been used in a query, the answer is no. You can't do this:

 with A
 as (
    -- query
 )
 select A.*
 from   A;

 -- this is a separate query
 select id
    ,   count(*)
 from   A
 group by 
        id

You can, however, combine CTEs in all kinds of ways, as long as you do it in a single statement. You can do this, which uses the hypothetical CTE A in two CTEs and the final query:

 with A
 as  (
    -- some query
 )
 ,   ACustomers
 as  (
     select *
     from   Customers
         join A
             on ....
 )
 ,   AVendors
 as  (
     select *
     from   Vendors
         join A
              on ....
 )
 select  A.StateId
    ,    ACount = COUNT(*)
    ,    CustomerCount = (select count(*) from ACustomers ac where ac.StateId = A.StateId )
    ,    VendorCount = (select count(*) from AVendors av where av.StateId = A.StateId )
 from    A
 group by 
        A.StateId