• 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
    FROM Test
    ORDER BY Test.Sales)


    Table is named Test


    Three fields:
    ID, Autonumber, primary key
    Sales, Currency
    DataDate, Date


    Thanks,

  • Re: Writing SQL


    I agree with Will, I do not see where you identify your Division field, however, this is what I come up with when I put a db together with just sales and a division field


    This is what is in my table


    Division Sales
    1 $100.00
    2 $200.00
    3 $50.00
    4 $300.00
    5 $25.00
    6 $400.00
    7 $500.00
    8 $600.00
    9 $700.00
    5 $30.00
    3 $25.00


    SQL
    1. SELECT TOP 4 Avg(Sales), Division
    2. FROM Test
    3. GROUP BY Division
    4. ORDER BY Avg(SALES) ASC;


    and this is the resulting query


    Average_Sales Division
    $27.50 5
    $37.50 3
    $100.00 1
    $200.00 2