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: Use SELECT INTO to create table (take derived table query and make a table based on it)
SELECT OrderId, SUM(NetAmount) AS TotalSales
    INTO ScottsNewTable
    FROM [Order Details]
    GROUP BY orderId

-- Step 2: rewrite the query to refer to the new table
SELECT Avg(TotalSales)
FROM ScottsNewTable