CODEWITHSUNDEEP
sqlsql-serveradventureworks
Dane Cameron
Dane Cameron

Reputation: 423

SQL Server - SELECT statement not returning results

Using the AdventureWorks database, I have been given a question to "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". However, my below SELECT statement does not return any results... What am I doing wrong?

SELECT scpii.LastName + ', ' + scpii.FirstName AS 'Customer Name', ssoh.Status AS 'Order Status', ssoh.OrderDate AS 'Date Ordered', SUM (ssod.OrderQty) AS 'Count of Items', AVG (ssod.OrderQty) AS 'Average Quantity'
FROM Sales.CustomerPII scpii
INNER JOIN Sales.SalesOrderHeader ssoh
ON ssoh.CustomerID = scpii.CustomerID
INNER JOIN Sales.SalesOrderDetail ssod
ON ssod.SalesOrderID = ssoh.SalesOrderID
GROUP BY scpii.LastName, scpii.FirstName, ssoh.Status, ssoh.OrderDate, ssod.OrderQty
HAVING SUM(ssod.OrderQty) > 300;

Upvotes: 1

Views: 176

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

  • You dont need to Group By on ProductID and Orderqty. If you do a groupping on them, you will be grouping it to a single order item level. So you will never be able to count more than 300
  • Count of items on order should really be sum of the Orderqty.

Try:

SELECT scpii.LastName + ', ' + scpii.FirstName AS 'Customer Name', 
       ssoh.Status AS 'Order Status', 
       ssoh.OrderDate AS 'Date Ordered', 
       SUM (ssod.OrderQty) AS 'Count of Items', 
       AVG (ssod.OrderQty) AS 'Average Quantity'
FROM Sales.CustomerPII scpii
INNER JOIN Sales.SalesOrderHeader ssoh
  ON ssoh.CustomerID = scpii.CustomerID
INNER JOIN Sales.SalesOrderDetail ssod
  ON ssod.SalesOrderID = ssoh.SalesOrderID
GROUP BY scpii.LastName, 
         scpii.FirstName, 
         ssoh.Status, 
         ssoh.OrderDate 
HAVING SUM (ssod.OrderQty) > 300;

Upvotes: 5

Related Questions