How to Fix the code given below so that it can satisfy the questions below: a. L

Question

How to Fix the code given below so that it can satisfy the questions below:

a. List all customer zip codes (wither they have an order or not)

b. Count the number of customers in that zip

c. Count the number of orders for that zip

d. Calculate the total spent in that zip

e. Sort the result by the most profitable zips (Total and # of customers)

USE tempdb

SELECT
COUNT(DISTINCT C.CustomerID) AS CountOfCustomers,
---Add Count of Number of Orders
SUM(OL.Quantity X OL.UnitPrice) AS OrderTotal,
C.PostalPostalCode
FROM
Sales.Orders O
INNER JOIN Sales.OrderLines OL ON OL.OrderID = O.OrderID
INNER JOIN Sales.Customers ON C.CustomerID = O.CustomerID
GROUP BY
OL.PostalPostalCode
ORDER BY
PostalPostalCode DESC
;

Explanation / Answer

If you have any doubts, please give me comment...

SELECT COUNT(DISTINCT C.CustomerID) AS CountOfCustomers, COUNT(OL.OrderID) AS CountOfOrders, SUM(OL.Quantity * OL.UnitPrice) AS OrderTotal, C.PostalPostalCode

FROM Sales.Orders O INNER JOIN Sales.OrderLines OL ON OL.OrderID = O.OrderID INNER JOIN Sales.Customers ON C.CustomerID = O.CustomerID

GROUP BY OL.PostalPostalCode

ORDER BY OrderTotal, CountOfCustomers DESC;

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.