Display Average Rating by Id with Count of Ids Mysql

I have an MYSQL table, where I want to get the average rating with a count of product id. Here is the sample of the table.

╔════╦═════════════╦══════════╦══════════╗
║ ID ║ product_Id  ║ rating   ║ status   ║
╠════╬═════════════╬══════════╬══════════╣
║  1 ║ 5           ║ 1        ║        1 ║
║  2 ║ 5           ║ 3        ║        1 ║
║  3 ║ 5           ║ 4        ║        1 ║
║  4 ║ 4           ║ 3        ║        1 ║
║  5 ║ 4           ║ 5        ║        1 ║
║  6 ║ 6           ║ 4        ║        1 ║
║  7 ║ 6           ║ 4        ║        1 ║
╚════╩═════════════╩══════════╩══════════╝

I have worked on this piece of code but didn’t get the desired result.

SELECT r.product_id, ROUND(AVG(r.rating),0) as avg_rating, COUNT(r.product_id) AS products_total FROM review r
                WHERE r.status = 1 
                GROUP BY r.product_id ASC

which gives me the result as, average rating (number of ratings for the same product Id)

3(3)
4(2)
4(2)

where I want results to be print as, average rating (number of products ids with the same Rating)

3(1)
4(2)

how can I attain the above results

Answer

If I understand, then you want two levels of aggregation:

SELECT avg_rating, COUNT(*)
FROM (SELECT r.product_id, AVG(r.rating) as avg_rating, COUNT(r.product_id) AS products_total
      FROM review r
      WHERE r.status = 1 
      GROUP BY r.product_id
     ) p
GROUP BY avg_rating

Advertisement

Answer