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 `CTE`s 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