The following samples demonstrates the application of derived table (cnum) to build complex queries:

USE Northwind

SELECT c.CategoryName, p.ProductName, p.UnitPrice, cnum.NoOfProducts
FROM Categories c
INNER JOIN Products p
ON c.CategoryID = p.CategoryID
INNER JOIN ( SELECT c.CategoryID, NoOfProducts = count(*)
FROM Categories c
INNER JOIN Products p
ON c.CategoryID = p.CategoryID
GROUP BY c.CategoryID) cnum
ON c.CategoryID = cnum.CategoryID
ORDER BY c.CategoryName