locked
Need some assistance RRS feed

  • Question

  • Trying to complete a project for my database management class. Was wondering is someone could help me out.

    Write an SQL statement to get the average, maximum, and minimum quantity per order stored in table “Sales.SalesOrderDetail”, column “SalesOrderID”, for order numbers 43660, 43670, and 43672. This query should be written as a single SQL statement.

    1.        Fill out the following table:

    Order number

    Average

    Maximum

    Minimum

    43660

    43670

    43672

    Monday, October 3, 2016 3:11 PM

Answers

  • I entered it like this 

    select SalesOrderID as 'Order Number', avg(quantity) as 'Average',max(quantity) as ' Maximum',min(quantity) as 'Minmum'
    from Sales.SalesOrderDetail 
    group by SalesOrderID, quantity

    order by SalesOrderID

    says invalid name quantity


    you don't need to use quantity in the group by column,  it is only returning it's aggregates.
    Sunday, October 9, 2016 10:33 AM

All replies

  • Use the below query to get the required output

    select avg(quantity),max(quantity),min(quantity)
    from tablename 
    group by ordernumber


    Please click Mark As Answer if my post helped.
    Thanks,
    Senthill

    Monday, October 3, 2016 3:14 PM
  • select SalesOrderID as 'Order Number', avg(quantity) as 'Average',max(quantity) as ' Maximum',min(quantity) as 'Minmum'
    from Sales.SalesOrderDetail
    group by SalesOrderID, quantity

    order by SalesOrderID


    Santosh Singh

    Monday, October 3, 2016 4:29 PM
  • Not doing the work means you are not learning. Do your own homework!
    Monday, October 3, 2016 4:35 PM
  • When I try it, it gives me an error message regarding quantity.
    Monday, October 3, 2016 6:32 PM
  • What is that error?

    Please share table definition too.

    The purpose was to give you hint on query formation, please share details as asked so that query can be formed.


    Santosh Singh


    Monday, October 3, 2016 6:39 PM
  • I entered it like this 

    select SalesOrderID as 'Order Number', avg(quantity) as 'Average',max(quantity) as ' Maximum',min(quantity) as 'Minmum'
    from Sales.SalesOrderDetail 
    group by SalesOrderID, quantity

    order by SalesOrderID

    says invalid name quantity

    Tuesday, October 4, 2016 2:22 PM
  • Hi Lilbum,

    If you can share table definition then you help you.

    share result of query.

    SELECT ORDINAL_POSITION, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
           , IS_NULLABLE
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'SalesOrderDetail '


    Please click Mark As Answer if my post helped.

    Tuesday, October 4, 2016 2:28 PM
  • I entered it like this 

    select SalesOrderID as 'Order Number', avg(quantity) as 'Average',max(quantity) as ' Maximum',min(quantity) as 'Minmum'
    from Sales.SalesOrderDetail 
    group by SalesOrderID, quantity

    order by SalesOrderID

    says invalid name quantity


    you don't need to use quantity in the group by column,  it is only returning it's aggregates.
    Sunday, October 9, 2016 10:33 AM