Example of Sub-Queries

Here is a sub query that has a sub-query in the "SELECT" clause of the main query, and another in the "FROM" Clause

use AdventureWorks

DECLARE @range datetime
set @range = '20030101'

SELECT AVG(T.Total) as Average, 
 (
  SELECT COUNT(*)
  FROM Sales.SalesOrderHeader
  WHERE OrderDate between @range and DATEADD(year,1,@range)
 ) as ORDERCOUNT
FROM ( 
  SELECT SUM(sh.TotalDue) Total, sh.CustomerID
  FROM Sales.SalesOrderHeader sh
  WHERE OrderDate between @range and DATEADD(year,1,@range)
  GROUP BY CustomerID
 ) AS t

Lets take a look at what this query actually does. Starting, I "DECLARE" a "datetime" variable, this is to make it more speedy to set the date I would like to check the year for. The main goal of this query is to show the average total for range of one year for all sales, and the "COUNT" for the year of sales done. What the first sub-query, that is located in the select clause, takes all the records from the "SalesOrderHeader" which is actually all the records from when the company started, then drops all the records that don't fall into the range, that was specified in the "SET" at the top.
The next sub-query is actally in the "FROM" clause, its actually used to make another table that takes all the records from the "SalesOrderHeader" and gets only the records in the range, same as the sub-query above. Then groups the records by there "CustomerID" this makes is so they are unique and then takes those grouped records and finds the "SUM" of the "TotalDue" column of these records. This sub-query used in the "FROM" clause makes it appear as a unique table, listing the "Total" sales of each "CustomerID" by taking this they can find out the average a person spends during the given year.

More To Come, CA.