none
AdventureWorks 2016 - Joins RRS feed

  • Question

  • I am currently working on a question that im having trouble with, I am new to SQL and having some trouble with Joins.

    The question must be answered with Joins or a Union, no subqueries of any kind.

    I have the following question: 

    List the orders customer name, order status, date ordered, count of items on the order, and average quantity ordered where the count of items on the order is greater than 300.

    So far this is what I have:

    SELECT FirstName, LastName, Status, OrderDate, OrderQty
    FROM Sales.Customer AS C
    JOIN Sales.SalesOrderHeader AS O on C.CustomerID=O.CustomerID
    JOIN Person.Person P on C.PersonId = P.BusinessEntityId
    JOIN Sales.SalesOrderDetail D on O.SalesOrderID = D.SalesOrderID

    Any advice is greatly appreciated


    • Edited by smatmart Monday, March 26, 2018 1:49 AM
    Monday, March 26, 2018 1:45 AM

All replies

  • I changed condition to have count of items > 30 (otherwise I got 0 results):

    SELECT P.FirstName, P.LastName, o.Status, o.OrderDate,

    AVG(D.OrderQty) as AverageQtyOrdered, count(d.ProductId) as ItemsCount FROM Sales.Customer AS C JOIN Sales.SalesOrderHeader AS O on C.CustomerID=O.CustomerID JOIN Person.Person P on C.PersonId = P.BusinessEntityId JOIN Sales.SalesOrderDetail D on O.SalesOrderID = D.SalesOrderID GROUP BY P.FirstName, P.LastName, o.Status, o.OrderDate HAVING count(d.ProductId) > 30



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, March 26, 2018 2:16 AM
    Moderator