LearnItFirst User Forum

-- What is the average sales price?
--            This must be done in two steps because you cannot take an aggregate of an aggregate
--            This is based on the Northwind database
SELECT Avg(TotalSales)
FROM (
    SELECT OrderId, SUM(NetAmount) AS TotalSales
    FROM [Order Details]
    GROUP BY orderId
) AS x

-- Step 1: Take derived table and create table variable from definition:
DECLARE @MyTable TABLE (OrderId INT, TotalSales MONEY)

-- Step 2: Populate the table variable with the derived table's result set
INSERT @MyTable
SELECT OrderId, SUM(NetAmount) AS TotalSales
    FROM [Order Details]
    GROUP BY orderId

-- Step 3: Replace derived table in original query with table variable
SELECT Avg(TotalSales)
FROM @MyTable