Keeping it simple and how to do multiple CTE in a query

I have this simple T-SQL query, it emits a bunch of columns from a table and also joins information from other related tables.

My data model is simple. I have a scheduled event, with participants. I need to know how many participants participate in each event.

My solution to this is to add a CTE that groups scheduled events and counts the number of participants.

This will allow me to join in that information per scheduled event. Keeping the query simple.

I like to keep my queries simple, however, If I ever in the future need to have additonal temporary results accessible during my simple query, what do I do?

I would really like it, if I could have multiple CTEs but I can't, right? What are my options here?

I've ruled out views and doing things at the application data layer. I prefer to isolated my SQL queries.

You can have multiple CTEs in one query, as well as reuse a CTE:

WITH    cte1 AS
        (
        SELECT  1 AS id
        ),
        cte2 AS
        (
        SELECT  2 AS id
        )
SELECT  *
FROM    cte1
UNION ALL
SELECT  *
FROM    cte2
UNION ALL
SELECT  *
FROM    cte1

Note, however, that SQL Server may reevaluate the CTE each time it is accessed, so if you are using values like RAND(), NEWID() etc., they may change between the CTE calls.

You certainly are able to have multiple CTEs in a single query expression. You just need to separate them with a comma. Here is an example. In the example below, there are two CTEs. One is named CategoryAndNumberOfProducts and the second is named ProductsOverTenDollars.

WITH CategoryAndNumberOfProducts (CategoryID, CategoryName, NumberOfProducts) AS
(
   SELECT
      CategoryID,
      CategoryName,
      (SELECT COUNT(1) FROM Products p
       WHERE p.CategoryID = c.CategoryID) as NumberOfProducts
   FROM Categories c
),

ProductsOverTenDollars (ProductID, CategoryID, ProductName, UnitPrice) AS
(
   SELECT
      ProductID,
      CategoryID,
      ProductName,
      UnitPrice
   FROM Products p
   WHERE UnitPrice > 10.0
)

SELECT c.CategoryName, c.NumberOfProducts,
      p.ProductName, p.UnitPrice
FROM ProductsOverTenDollars p
   INNER JOIN CategoryAndNumberOfProducts c ON
      p.CategoryID = c.CategoryID
ORDER BY ProductName