Help with SQL errors This is schema and I\'m having some errors getting result A
ID: 3750082 • Letter: H
Question
Help with SQL errors
This is schema and I'm having some errors getting result
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 cust.CustomerID = orde.CustomerID
join OrderItems ordi on orde.CustomerID = ordi.OrderID
Group by cust.EmailAddress
Having count(orde.OrderID)>0
===================================================
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.Quantity' 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
select DISTINCT cust.EmailAddress, count(orde.OrderID) as "Total Orders",((ordi.Itemprice-ordi.DiscountAmount)*ordi.Quantity) as "Total Purchase Amount"
from Customers cust, OrderItems ordi, Orders orde
INNER JOIN CUSTOMERS cust.CustomerID = orde.CustomerID
INNER JOIN ORDERITEMS ON orde.CustomerID = ordi.OrderID
where count(orde.OrderID)>0
join
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.