Display all the categories and the total amount of profit fo

Display all the categories and the total amount of profit for each category. Rename the calculated field as ‘CATEGORY_PROFIT’. Category profit is calculated as number of books sold times profit (retail – cost) in that category. Round the profit calculation to the nearest full value. Order the result set in the descending order of highest calculated profit.

ORDERS Order CUSTOMERS Customer Customer Orderd late Lastname Ship date Firstname Shipstreet Emal Shipcity Address Shipstate Shipzip BOOKAUTHOR AUTHOR State BOOKS Shipcost SBN AuthorlD Referred SBN AuthorID Lname ORDERITEMS Region Title Fname Order# Pubdate LA tem# Publ D SBN Cost Quantity Retail Discount PUBLISHER Category Paid each PublD Name PROMOTION Phone Contact Gift Minretaj Max retai FIGURE 1-5 JustLee Books\'s table structures after normalization

Solution

SELECT Round(SUM(Quantity*(retail-cost)), 0) AS `CATEGORY_PROFIT` FROM BOOKS INNER JOIN ORDERITEMS WHERE 1 ORDERBY CATEGORY_PROFIT DESC;