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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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