Display ShipCity ShipState and Total Sales Determine ShipCit

Display ShipCity, ShipState and Total Sales. Determine ShipCity and ShipState with Lowest total Sales [Total Sales = SUM(QUANTITY*Retail)].

The tables already exist within my database, I just have to display the information above correctly, given the tables and attributes from the picture inserted.

I have been trying to work on it, and this is the code I currently have, but it does not execute correctly. What do I need to fix?

SELECT O.ShipCity, O.ShipState, SUM (T.QUANTITY*B.Retail) AS TotalSales
FROM Orders O, ORDERITEMS T , Books B
WHERE SUM (T.QUANTITY*B.Retail)=(SELECT MIN (SUM (T.QUANTITY*B.Retail) FROM Books)
GROUP BY O.ShipCity, O.ShipState

BOOK AUTHOR 8 ISBN 8 Author Author 8 Author D Lname Frame Books 8 ISBN Title pubDate oo E Cost Retail Category Customers 8 cust D LastName FirstName Address State Referred Publisher publD Name Contact phone ORDERITEMS OrderID ltemNo ISBN QUANTITY Orders 8 OrderiD Cust D OrderDate ShipDate ShipStreet ShipCity Ships Shipzip promotion gift minretail maxretail

Solution

Answer:-

SELECT O.ShipCity, O.ShipState, SUM (T.QUANTITY*B.Retail) AS TotalSales
FROM Orders O, ORDERITEMS T , Books B
WHERE SUM (T.QUANTITY*B.Retail)= MIN(T.QUANTITY*B.Retail) FROM Books
GROUP BY O.ShipCity, O.ShipState