Get a list of the number of products reviewed for every retailer
I need help with the SQL query for the following question:
1. Get a list of the number of products reviewed for every retailer
Here is the information on the field names:
The table name is: Reviews
Review Form Fields
Product/ModelName:
Product/Category:
Product/Price:
Retailer/Name:
Retailer/ZipCode:
Retailer/City:
Retailer/State:
Product/On Sale:
Manufacturer/Name:
Manufacturer/Rebate:
Review/UserId:
Review/Rating:
Review/Date:
Review/Text:
Example of Review Form Filled out
ProductModelName: Galaxy S4
ProductCategory: Smart Phone
ProductPrice: 499
RetailerName: Bestbuy
RetailerZipCode: 44114
RetailerCity: Cleveland
RetailerState: OH
ProductOnSale: No
ManufacturerName: Samsung
ManufacturerRebate: No
ReviewUserId: xmm473
ReviewRating: 3
ReviewDate: 6/10/2012
ReviewText: overheats after 2 hours
TABLES
The tables based on the given forms are shown below.
Product ( PID, ModelName, Category, Price )
Retailer ( RID, Name, City, State, ZipCode )
SoldBy ( SID, RetailerID, ProductID )
Review ( ReviewID, UserID, ProductID, ReviewDate, Rating, Review )
Manufacturer ( MID, Name )
User ( UID )
The COUNT() is an aggregate function which returns the number of rows either in the whole table or entity wise.
Here, the number of products which got reviewed by customer, for each retailer, is required.
Hence, COUNT() will be used on the product column from the Review table.
Next, number of products reviewed is required retailer-wise.
The GROUP BY clause is used on the retailer column from the Review table.
Step by step
Solved in 3 steps
