CTE within SUM or COUNT!

Starting with this T-SQL and improving upon it gives us:

SELECT c.CurrencyId AS 'Id'
c.CurrencyCode,
COUNT(i.TotalAmount) AS 'OpenInvoices',
COALESCE(SUM(i.TotalAmount), 0) AS 'Balance',
(SELECT COUNT(TotalAmount) FROM #Invoices
   WHERE DueDate < GETDATE() AND Id = CurrencyId) AS 'OpenInvoices',
COALESCE((SELECT SUM(TotalAmount) FROM #Invoices
   WHERE DueDate < GETDATE() AND Id = CurrencyId), 0) AS 'Balance',
FROM #Currencies c
LEFT JOIN #Invoices i ON c.CurrencyId = i.CurrencyId
GROUP BY CurrencyCode

 
 

Note that if you want to use a column from another table not specified in your CTE that it better be one of the columns returned in the select statement. Otherwise, expect an error. To that end I've looped CurrencyId into the list of columns returned and have fudged its name so there is no ambiguity when matching CurrencyId to CurrencyId in my CTEs without a leading c. or i. to distinguish them.