CODEWITHSUNDEEP
phpmysqlsqlcount
Saraswathi Apavoo
Saraswathi Apavoo

Reputation: 371

mysql query calculate rating sum

I have following table

id item_id rating 
 1    10     1
 2    10     2
 3    10     2
 4    10     3
 5    10     3
 6    10     3
 7    10     4

How can I write a query so that I can get result as follows:

$ratings = array(
    1 => 1,
    2 => 2,
    3 => 3,
    4 => 1,
    5 => 0
);

I need to use this query to write a php function to calculate average ratings by this function:

$total = 0;
$count = 0;
foreach($ratings as $number=>$frequency) {
  $total += $number * $frequency;
  $count += $frequency;
}
return $total / $count;

Upvotes: 2

Views: 2126

Answers (2)

John Woo
John Woo

Reputation: 263723

The basic COUNTing of records on the table won't produce 5 because there is no value 5 on the rating. But generating subquery which contains values from 1-5 and joining them using LEFT JOIN will do your desired result.

SELECT a.rating, COUNT(b.rating) totalCount
FROM
    (
      SELECT 1 rating UNION SELECT 2 UNION
      SELECT 3 UNION SELECT 4 UNION
      SELECT 5
    ) a
    LEFT JOIN tableName b
      ON a.rating = b.rating
GROUP BY a.rating

Upvotes: 3

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

SELECT
    rating,
    COUNT(rating) as Count
FROM rating
GROUP BY rating

Upvotes: 3

Related Questions