- Looking For Help On 19 18 Create A View Named Category Region Over The Category Region Store Salestranaction I 1 (107.33 KiB) Viewed 67 times
-- over the category, region, store,
salestranaction, includes,
-- and product tables that summarizes total
quantity sold by region and category. The view
-- should have columns:
-- categoryid, categoryname, regionid,
regionname, totalsales
CREATE VIEW category_region AS
SELECT c.categoryid, c.categoryname, r.regionid, r.regionname,
i.quantity AS totalsales
FROM region r
INNER JOIN store s ON r.regionid = s.regionid
INNER JOIN salestransaction t ON s.storeid = t.storeid
INNER JOIN includes i ON t.tid = i.tid
INNER JOIN product p ON i.productid = p.productid
INNER JOIN category c ON p.categoryid = c.categoryid;
Question 19 Using the view created in 18, which region
has the most sales for
-- each category.
-- you should get the result
-- categoryname regionname totalsales
-- Electronics Chicagoland 6
-- Climbing Indiana 17
-- Camping Tristate 9
-- Footwear Tristate 20
-- Cycling Chicagoland 13
This is what I have so far but it is not giving me the desired
output
select categoryname, regionname, max(totalsales) as
totalsales
from category_region
group by categoryname, regionid
order by categoryname;
VENDOR VendorID VendorName PRODUCT ProductID ProductPrice ProductName VendorID (FK) CategoryID (FK) REGION RegionID Region Name CATEGORY CategoryID CategoryName STORE StoreID StoreZip RegionID (FK) INCLUDES ProductID (FK) TID (FK) Quantity SALES TRANSACTION TID TDate StoreID (FK) CustomerID (FK) CUSTOMER CustomerID CustomerName CustomerZip