Execute the following script in Query Editor to demonstrate the use of CROSS APPLY between an outer and a correlated inner query with the GROUP BY clause. Only matching results are returned:

-- The outer query adds customer name to the inner query aggregation results

SELECT [Customer]=s.Name,

-- This is a special money type currency formatting option

[Total$ Sales] = '$'+convert(varchar,convert(money,SalesAmount.OrderTotal),1)

FROM   Sales.Customer as c

-- The customer name is in this table

INNER JOIN Sales.Store as s

    ON s.CustomerID = c.CustomerID

-- The inner query is a correlated GROUP BY subquery

CROSS APPLY (SELECT soh.CustomerId, sum(sod.LineTotal) as OrderTotal

               FROM   Sales.SalesOrderHeader as soh

               INNER JOIN Sales.SalesOrderDetail as sod

                   ON sod.SalesOrderId = soh.SalesOrderId

               -- This is the correlation to the outer query

               WHERE soh.CustomerId = c.CustomerId

               -- Filter data

               AND     YEAR(OrderDate)= 2004

               AND     MONTH(OrderDate) = 1

               GROUP by soh.CustomerId) as SalesAmount

ORDER BY [Customer]