get no value when I run the query to this question

user_15326169 0
Viewed 45

The assignment question is...

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.

I'm using Adventure Works 2019 for the assignment.

The answer I have been able to come up with is....

SELECT
    LastName + ', ' + FirstName AS 'Customer Name',
    ssoh.Status AS 'Order Status',
    ssoh.OrderDate AS 'Date Order',
    SUM(ssod.Orderqty) AS 'Count of Items',
    AVG(ssod.Orderqty) AS 'Average Quantity'
FROM
    Person.Person pp
    JOIN Sales.SalesOrderHeader ssoh ON pp.BusinessEntityID = ssoh.CustomerID
    JOIN Sales.SalesOrderDetail ssod on ssoh.SalesOrderID = ssod.SalesOrderid
GROUP BY
    LastName + ', ' + FirstName,
    ssoh.OrderDate,
    ssoh.Status
HAVING
    SUM(ssod.OrderQty) > 300

When I change the inner join to an outer join I get null values for the customer name. I doubled checked the "customerID" foreign key to make sure it the same as the primary key "BusinessEntityID" and get results. Any ideas would be greatly appreciated. Thanks

adventureworks ssms sql
2 Answers

Based on having a quick look at the schema online (I don't have it installed), it's my understanding that you need to join Person.Person through Sales.Customer to Sales.SalesOrderHeader. So perhaps try the following:

FROM
    Person.Person pp
    JOIN Sales.Customer sc ON pp.BusinessEntityID= sc.PersonID
    JOIN Sales.SalesOrderHeader ssoh ON sc.CustomerID= ssoh.CustomerID
    JOIN Sales.SalesOrderDetail ssod on ssoh.SalesOrderID = ssod.SalesOrderid
user_2894300

First, grouping by LastName + ', ' + FirstName will provide you inaccurate results. Because there are approx 380 names with duplications. Sample:

enter image description here

Second, Manachi is right about the join.

So, your final query should be like this:

WITH cte AS (
    SELECT
        BusinessEntityID AS CustomerId,
        LastName + ', ' + FirstName AS 'Customer Name',
        ssoh.Status AS 'Order Status',
        ssoh.OrderDate AS 'Date Order',
        SUM(ssod.Orderqty) AS 'Count of Items',
        AVG(ssod.Orderqty) AS 'Average Quantity'
    FROM
        Person.Person pp
        JOIN Sales.Customer c ON c.PersonID = pp.BusinessEntityID
        JOIN Sales.SalesOrderHeader ssoh ON c.CustomerID = ssoh.CustomerID
        JOIN Sales.SalesOrderDetail ssod on ssoh.SalesOrderID = ssod.SalesOrderid
    GROUP BY
        BusinessEntityID,
        LastName + ', ' + FirstName,
        ssoh.OrderDate,
        ssoh.Status
    HAVING
        SUM(ssod.OrderQty) > 300
)
SELECT
    [Customer Name],
    [Order Status],
    [Date Order],
    [Count of Items],
    [Average Quantity]
    FROM cte
user_45710400
Related