How can I get this code to compile what I need in SQL?

Question

I am having issues getting this code to work. Using AdventureWorks2012, my task is to list the order's 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. This is what I have come up with but it won't compile. Any advice?

SELECT soh.CustomerID, soh.Status, soh.OrderDate, COUNT(sod.OrderQty), AVG(sod.OrderQty)
FROM Sales.SalesOrderHeader AS soh
JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID
RIGHT JOIN Sales.SalesOrderDetail On soh.SalesOrderID = sod.SalesOrderID
SELECT SalesOrderID, sum(OrderQty) AS 'Total Items'
FROM sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING sum(OrderQty) > 300;

Answer 1

You probably want to join these two queries that you provided. You are missing the GROUP BY clause in the first query. One solution how to put them together is to use IN:

SELECT soh.CustomerID, 
    soh.Status, 
    soh.OrderDate, 
    COUNT(sod.OrderQty), 
    AVG(sod.OrderQty)
FROM Sales.SalesOrderHeader AS soh
JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID
WHERE soh.SalesOrderID IN (
    SELECT SalesOrderID
    FROM sales.SalesOrderDetail
    GROUP BY SalesOrderID
    HAVING sum(OrderQty) > 300
)
GROUP BY soh.CustomerID, 
    soh.Status, 
    soh.OrderDate

Answer 2

Thanks for the reply. I believe I have the correct answer here:

    SELECT p.LastName, p.FirstName, soh.Status, soh.OrderDate, SUM(sod.OrderQty) AS 'Total 
    Items', AVG(sod.OrderQty) AS 'Average Quantity'
    FROM Sales.SalesOrderHeader AS soh
    JOIN Sales.Customer AS C ON c.CustomerID = soh.CustomerID
    JOIN Person.BusinessEntityContact AS bec ON bec.PersonID = c.PersonID
    JOIN Person.Person AS p ON p.BusinessEntityID = bec.PersonID
    JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID
    GROUP BY p.LastName, p.FirstName, soh.Status, soh.OrderDate
    HAVING sum(OrderQty) > 300;