• 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


  • 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

    SELECT TOP 4 Avg(Sales), Division
    FROM Test
    GROUP BY Division

    and this is the resulting query

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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!