SQL error Please fix A manager wants to know the email address, number or orders
ID: 3750217 • Letter: S
Question
SQL error
Please fix
A manager wants to know the email address, number or orders, and the total amount of purchases made by each customer. Create a summary query that returns these three items for each customer that has orders. Hint: The total is the price minus the discount, multiplied by the quantity
=========================================
select cust.EmailAddress, count(orde.OrderID) as "Total Orders", ((ordi.Itemprice-ordi.DiscountAmount)* ordi.Quantity) as "Total Purchase Amount"
from Customers cust
join Orders orde on orde.CustomerID = cust.CustomerID
join OrderItems ordi on ordi.OrderID = orde.OrderID
WHERE cust.CustomerID = orde.CustomerID AND orde.OrderID = ordi.OrderID
group by cust.EmailAddress
================================
Error message
Column 'OrderItems.ItemPrice' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Column 'OrderItems.DiscountAmount' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Column 'OrderItems.ItemPrice' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Administrators AdminlD EmailAddress Password FirstName LastName Orderltems ItemID OrderID ProductiD temPrice DiscountAmount Products ProductiD CategoryID ProductName Description ListPrice DiscountPercent DateAdded Orders Addresses OrderlD CustomerlD OrderDate ShipAmount TaxAmount ShipDate ShipAddressID AddresSID CustomeriD Line1 Line2 Customers CustomerID EmailAddress Password FirstName State ZipCode ShippingAddressID BillingAddressID Categories CategoryID CategoryName CardNumber CardExpires BillingAddressIDExplanation / Answer
Answer)
The problem with the above query is that no aggregate function was used and thus group by failed.
The revised query will be:
select cust.EmailAddress, count(orde.OrderID) as "Total Orders", sum((ordi.Itemprice-ordi.DiscountAmount)* ordi.Quantity) as "Total Purchase Amount"
from Customers cust
join Orders orde on orde.CustomerID = cust.CustomerID
join OrderItems ordi on ordi.OrderID = orde.OrderID
WHERE cust.CustomerID = orde.CustomerID AND orde.OrderID = ordi.OrderID
group by cust.EmailAddress;
Here, the errors will be removed and sum() is used to calculate the "Total Purchase Amount".
Thus an aggregate function was necessary which caused the error.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.