title: MySQL - best way to retrieve rating and reviews summary from a basic review table e.g. google
body: <p><a href="https://i.stack.imgur.com/FMTlt.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/FMTlt.png" alt="AIM"></a></p>
  
  <p>I have build a basic table for storing my review &amp; ratings and want to retrieve details for above plugin .</p>
  
  <pre><code>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
  </code></pre>
  
  <p>SQL for getting summary</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Results:</p>
  
  <p><a href="https://i.stack.imgur.com/ae81b.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ae81b.png" alt="SQL Results"></a></p>
  
  <p>I am sure there is a better way (Optimized) to do it but I cant guess so I needed help again.</p>
  
  <p><strong>Question: How can i optimize it more ?</strong></p>