I am trying to find the average of the the 4 lowest dollar sales out of an 8 week period by division. So I need one number for each division 11 divisions 11 different averages. Below, the current sql gives me the average for the 4 lowest dollar sales from the total 11 divisions instead of breaking it out by division. Can someone help in writing the sql?
SELECT Avg(Sales) AS AvgSmallest4
FROM (SELECT TOP 4 Test.Sales
ORDER BY Test.Sales)
Table is named Test
ID, Autonumber, primary key