DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to use CROSS APPLY with an aggregation subquery?

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

*/

 

Related article:

 

All the Fuss about CROSS APPLY

 

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE