Execute the following T-SQL script in Management Studio Query Editor to demonstrate the use of CROSS APPLY.

The following CROSS APPLY main query represent a special "JOIN " between an outer and a correlated inner query with the GROUP BY clause. Only matching results are returned:

USE AdventureWorks;

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

-- SQL cross apply - SQL group by - SQL correlated subquery

SELECT

  [Customer] = s.Name,

  -- This is a special money data 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]

GO

/* Partial results

Customer                      Total$ Sales

Activity Center               $9,630.73

All Cycle Shop                $167.98

All Seasons Sports Supply     $2,159.35

Amalgamated Parts Shop        $29,625.69

Area Bike Accessories         $57,449.60

Basic Bike Company            $65.99

Best Cycle Store              $37,202.78

Best o' Bikes                 $4,443.73

Better Bike Shop              $29,140.80

Bicycle Exporters             $8,043.03

*/