locked
Top 5 products with the most number of units ordered RRS feed

  • Question

  • I have the following tables:

     

     

    Company

    Companyid

    Address

    City

    State

    ZipCode

     

     

    Order

    OrderId PK

    OrderAmount

    OrderDate

    Companyid FK

     

    OrderItem

    OrderItemId PK

    OrderId FK

    Units

    Productid FK

     

    Product

    Productid PK

    Amount

     

     

    Now, I need to write a query to get the top 5 products having the most number of units ordered for a particular ZipCode (x) and for the month of October.

     

    The output should read the following:

     

    Productid, amount, units, ordered, zip

     

     

    Can anyone help?


    Kajoo
    Friday, September 4, 2009 11:27 PM

Answers

  • Try this.  (I don't know what your "Ordered" column in your output is supposed to be, so I guessed it's the Order Date).

    select top (5)
       i.ProductID
      ,p.Amount
      ,i.Units
      ,o.OrderDate
      ,c.Zip
    from
      OrderItem i
      join Order o on i.OrderID=o.OrderID
      join Product p on i.ProductID=p.ProductID
      join Company c on o.CompanyID=c.CompanyID
    where
      c.ZipCode='x' and
      h.OrderDate>='2008-10-01' and h.OrderDate<'2008-11-01'
    order by
      i.Units desc

    This will give you the top 5 individual order line items with the most quantities.

    But perhaps you're looking for top 5 products in terms of TOTAL sales during the month of October.  That would involve adding a GROUP BY to the query.


    --Brad (My Blog)
    • Marked as answer by Kajoo Wednesday, September 9, 2009 4:43 PM
    Friday, September 4, 2009 11:40 PM

All replies

  • Try this.  (I don't know what your "Ordered" column in your output is supposed to be, so I guessed it's the Order Date).

    select top (5)
       i.ProductID
      ,p.Amount
      ,i.Units
      ,o.OrderDate
      ,c.Zip
    from
      OrderItem i
      join Order o on i.OrderID=o.OrderID
      join Product p on i.ProductID=p.ProductID
      join Company c on o.CompanyID=c.CompanyID
    where
      c.ZipCode='x' and
      h.OrderDate>='2008-10-01' and h.OrderDate<'2008-11-01'
    order by
      i.Units desc

    This will give you the top 5 individual order line items with the most quantities.

    But perhaps you're looking for top 5 products in terms of TOTAL sales during the month of October.  That would involve adding a GROUP BY to the query.


    --Brad (My Blog)
    • Marked as answer by Kajoo Wednesday, September 9, 2009 4:43 PM
    Friday, September 4, 2009 11:40 PM
  • Try this (it is for all zip codes, you can add predicate to filter on one):

    SELECT Productid, Amount, total_units, Zipcode, rk AS ordered
    FROM (
    SELECT P.Productid, P.Amount, C.Zipcode,
           SUM(I.Units) AS total_units,
           ROW_NUMBER() OVER(PARTITION BY C.Zipcode
                             ORDER BY SUM(I.Units) DESC) AS rk
    FROM Product AS P
    JOIN OrderItem AS I
      ON P.Productid = I.Productid
    JOIN [Order] AS O 
      ON O.OrderId = I.OrderId
    JOIN Company AS C
      ON C.Companyid = O.Companyid
    WHERE O.OrderDate >= '20091001'
      AND O.OrderDate <  '20091101'
    GROUP BY P.Productid, P.Amount, C.Zipcode) AS T
    WHERE rk <= 5
    ORDER BY Zipcode, rk; 

    Plamen Ratchev
    Saturday, September 5, 2009 2:20 AM