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 build view from its definition:
CREATE VIEW MyView
AS
SELECT OrderId, SUM(NetAmount) AS TotalSales
    FROM [Order Details]
    GROUP BY orderId

-- Step 2: rewrite to use view
SELECT Avg(TotalSales)
FROM MyView