Search code examples
mysqlratingrating-system

MySQL - best way to retrieve rating and reviews summary from a basic review table e.g. google

AIM

I have build a basic table for storing my review & ratings and want to retrieve details for above plugin .

review_id | company_id | user_id | rating | review
1           1            1         3        Great
2           1            2         5        Thank you
3           1            3         5        Great
4           1            4         4        Thank you
5           1            5         1        Great
6           1            6         2        Thank you
7           2            5         1        Great
8           2            6         2        Thank you

SQL for getting summary

SELECT company_id as cid, ROUND(AVG(rating)) AS rate, COUNT(*) AS review_count, 
(SELECT COUNT(*) FROM `reviews` WHERE `rating` = 5 AND `company_id` = cid) AS rating_5,
((SELECT COUNT(*) FROM `reviews` WHERE `rating` = 5 AND `company_id` = cid) / COUNT(*) * 100) AS rating_5_avg,
(SELECT COUNT(*) FROM `reviews` WHERE `rating` = 4 AND `company_id` = cid) AS rating_4,
((SELECT COUNT(*) FROM `reviews` WHERE `rating` = 4 AND `company_id` = cid) / COUNT(*) * 100) AS rating_4_avg,
(SELECT COUNT(*) FROM `reviews` WHERE `rating` = 3 AND `company_id` = cid) AS rating_3,
((SELECT COUNT(*) FROM `reviews` WHERE `rating` = 3 AND `company_id` = cid) / COUNT(*) * 100) AS rating_3_avg,
(SELECT COUNT(*) FROM `reviews` WHERE `rating` = 2 AND `company_id` = cid) AS rating_2,
((SELECT COUNT(*) FROM `reviews` WHERE `rating` = 2 AND `company_id` = cid) / COUNT(*) * 100) AS rating_2_avg,
(SELECT COUNT(*) FROM `reviews` WHERE `rating` = 1 AND `company_id` = cid) AS rating_1,
((SELECT COUNT(*) FROM `reviews` WHERE `rating` = 1 AND `company_id` = cid) / COUNT(*) * 100) AS rating_1_avg

FROM `reviews` GROUP BY company_id

Results:

SQL Results

I am sure there is a better way (Optimized) to do it but I cant guess so I needed help again.

Question: How can i optimize it more ?

Solution

  • You can use conditional aggregation for this task:

    SELECT company_id as cid, 
       ROUND(AVG(rating)) AS rate, 
       COUNT(*) AS review_count,
       COUNT(CASE WHEN `rating` = 5 THEN 1 END) AS rating_5,
       COUNT(CASE WHEN `rating` = 5 THEN 1 END) / (COUNT(*) * 100) AS rating_5_avg,
        ... etc 
FROM `reviews` 
GROUP BY company_id

    Using an aggregate that contains a CASE expression:

    COUNT(CASE WHEN `rating` = 5 THEN 1 END)

    you can obtain a count on a subset of company_id records based on the value of rating field.