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 apply correlated subquery with averaging?

Execute the following Microsoft SQL Server T-SQL scripts in SSMS Query Editor demonstrate correlated subqueries.

-- SQL correlated (o.ProductID = ProductID) subquery
-- list all the order ids with order quantity in the bottom quartile

SELECT DISTINCT o.OrderId, o.ProductID

FROM   Northwind.dbo.[Order Details] o

WHERE  Quantity <= (SELECT avg(Quantity) * .25

                    FROM   Northwind.dbo.[Order Details]

                    WHERE  o.ProductID = ProductID)

                   

/* OrderId ProductID

10609       1

10700       1

10838       1

11003       1

11005       1

10851       2

10722       2  .... */

 

------------

 

-- Create table for demo purposes

SELECT * INTO dbo.Department from AdventureWorks.HumanResources.Department

GO

 

-- UPDATE with correlated subquery

UPDATE D

SET Name =

(

 SELECT Name FROM dbo.Department

 WHERE DepartmentID = D.DepartmentID

)

FROM AdventureWorks.HumanResources.Department D;

 

 

Related article:

 

http://www.sqlusa.com/bestpractices/derivedcorrelated/

 

 
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