1

I have below tables:
ProductsTable (has product details),
UsersTable (has all application users),
RatingCategry (category for which user can rate a products like Quality, Cost etc.) and
UserRatingsforProductsTable (Users rating for a product for any RatingCategory) for a Mobile Application.

Products (ProductId, ProductName… other details like cost etc.)

UsersTable (UserId, UserName… other details like DateOfJoin etc.)

RatingCategory (RatingCategoryId, CategoryName)
**Data:**
1         Quality
2         Cost

UserRatingsforProductsTable (RatingId, UserId, ProductId, RatingCategoryId, Score)
**Data:**
1         5006  20  1   4.5

2         5006  20  2   5.0

3         5600  23  1   3.5

4         7800   20  1   2.5

5         8900   34  1   4.0

6         8900   34  2    4.5

What will be the best and faster query for a Mobile Application to show “Rating ScoreTotal” in the Products list (ProductId, ProductName, Cost, RatingScore)? Shall I do calculation in the Query or Add a new column in Products table that will be auto updated when each Rating added into UserRatingsforProductsTable?

The expected output should be: 
ProductId, ProductName, Cost, OverallRatingForThisProd
---------  -----------  ----- ------------------------
*** OverAllRating = Average of Ratings from All Categories for the Specific Product from UserRatingsforProductsTable.

If the answer will be Query instead of adding a Column, can you please advise an optimized query?

Thank you.

  • I don't think adding a new column will be efficiently, can you add in your question you expected result? – oNare Jul 13 '15 at 16:43
  • I think you are right @oNare. I have added expected result in original question as well. Thank you for your help. – Prabhat Jul 13 '15 at 17:14
  • Nice @Prabhat , I posted 1 query as example of what you could do. – oNare Jul 13 '15 at 18:16
0

I don't think adding a new column will be efficiently and you'll need to do more things like adding TRIGGERs to update the field and you'll get the newColumnvalue in every row for the table UserRatingsForProductsTable.

Information:

create Table Products(
  ProductId INT(4),
  ProductName VARCHAR(75),
  Cost DECIMAL(10,2),
PRIMARY KEY (`ProductId`));

CREATE TABLE UserRatingsForProductsTable(
  RatingId int(6) auto_increment,
  UserId int(5),
  ProductId int(4),
  RatingCategoryId int(2),
  Score decimal(5,2),
PRIMARY KEY (`RatingId`));

insert into Products select 20,'Product1',23.33;
insert into Products select 23,'Product2',223.33;
insert into Products select 34,'Product3',323.33;

insert into UserRatingsForProductsTable select 1,5006,20,1,4.5;
insert into UserRatingsForProductsTable select 2,5006,20,2,5.5;
insert into UserRatingsForProductsTable select 3,5600,23,1,3.5;
insert into UserRatingsForProductsTable select 4,7800,20,1,2.5;
insert into UserRatingsForProductsTable select 5,8900,34,1,4.0;
insert into UserRatingsForProductsTable select 6,8900,34,2,4.5;

Query:

SELECT
    p.ProductId,
    p.ProductName,
    p.Cost,
    ROUND(sum(URPT.Score)/(COUNT(DISTINCT URPT.RatingCategoryId)*COUNT(URPT.UserId)),1) as OverallRatingForThisProd
FROM Products as p
LEFT JOIN UserRatingsForProductsTable AS URPT ON (URPT.ProductId=p.ProductId)
GROUP BY p.ProductId
ORDER BY p.ProductId;

Try it on SQLFiddle.

  • You might want to provide some hints about proper indexes for such query to be executed efficiently. – jkavalik Jul 13 '15 at 18:37
  • Thanks @oNare for this help. I think the calculation for OverallRatingForThisProd will be little different. Please advise. Please Check this quora.com/… – Prabhat Jul 14 '15 at 17:50
  • I have changed the query with your formula, can you try it again? – oNare Jul 14 '15 at 18:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.