Calculating the mean rating for each shop with MySQL

Question

Calculating the mean rating for each shop with MySQL

I am trying to combine the shop table with the rating table in order to fetch a list of all shops along with their individual ratings. However, my current query only returns shops that have ratings in the rating table. I want to display a rating of 0 for shops that do not have any ratings, and the actual rating for those that do.

shop table:-

id  shop name      
  1     shop_1      
  2     shop_2      
  3     shop_3      
  4     shop_4

rating table

id  shop_id  rating      
  1     1      3      
  2     4      2

Query:

$this->db->select('shop.*,shop.id as shop_id');
    $this->db->select('COALESCE(ROUND(AVG(rat.rating),1),0) as avgRate');
    $this->db->from('shop');
    $this->db->join('rating_reviews as rat', 'rat.shop=shop.id', 'left');
    $this->db->get()->result_array();

current output:

  id  shop_id      avgRate       
   1     1            3      
   2     4            2

Expected output:

 id  shop_id      avgRate     
   1     1            3     
   2     2            0           //(no rating given for this shop)    
   3     3            0           //(no rating given for this shop)       
   4     4            2

php mysql sql codeigniter join

Answer 1

Answer №1

Here is a different method that eliminates the need for using JOIN:

select
  distinct shop_id,
  (
     select coalesce(round(avg(rating), 1), 0)
     from ratings where shop.id=ratings.shop_id
  ) as average_rating
from shops

Answer 2

Here is a different method that eliminates the need for using JOIN:

select
  distinct shop_id,
  (
     select coalesce(round(avg(rating), 1), 0)
     from ratings where shop.id=ratings.shop_id
  ) as average_rating
from shops

Answer 3

Answer №2

Understanding Aggregate Functions

If you're encountering an error, it might be because functions like AVG and COUNT are considered as aggregate functions, meaning they consolidate all the data and usually produce a single result.

For instance:

Imagine there's a table named shops with id as its Primary Key
The table contains 35 shops
The Primary Key has no gaps (ranging from 1 to 35)

In such cases, the following queries will yield only one row/result:

# Query                          # Output:  # FIELD_1 # FIELD_2 (second statement)
SELECT COUNT(shop.id) FROM shop             # 35
SELECT shop.id, COUNT(shop.id) FROM shop    # 1       # 35

Both aforementioned queries would generate just one result. The first query counts the number of shops (35), while the second one also displays the ID of the first shop (1).

Your specific query operates on the same principle; similar to COUNT, the AVG function is an aggregate function that produces a singular result from the query.

SELECT 
    shop.*, shop.id as shop_id,
    COALESCE(ROUND(AVG(rat.rating),1),0) as avgRate
FROM shop
    LEFT JOIN rating_reviews AS rat ON rat.shop=shop.id

-- Result: All fields from `shop` for the first shop followed by [shop_id] and [avgRate]
-- For instance...
# shop.id # shop.name # shop.type   # shop_id # avgRate
# 1       # Tesco     # Supermarket # 1       # 3.5

Nevertheless, there are two methods to bypass this behavior:

Using GROUP BY

SELECT i, AVG(...) AS a FROM ... LEFT JOIN ... GROUP BY i

Utilizing Nested SELECT statements

SELECT i, (SELECT AVG(...) FROM ... WHERE ...) AS a FROM ...

GROUP BY Clause

SELECT 
    shop.*, shop.id as shop_id,
    COALESCE(ROUND(AVG(rat.rating),1),0) as avgRate
FROM shop
    LEFT JOIN rating_reviews AS rat ON rat.shop=shop.id
GROUP BY shop.id

Nested SELECT Statements

SELECT
    shop.*, shop.id as shop_id,
    (SELECT
         COALESCE(ROUND(AVG(rat.rating),1),0)
     FROM rating_reviews as rat
     WHERE rat.shop=shop.id
    ) as avgRate
FROM shop

Answer 4

Understanding Aggregate Functions

If you're encountering an error, it might be because functions like AVG and COUNT are considered as aggregate functions, meaning they consolidate all the data and usually produce a single result.

For instance:

Imagine there's a table named shops with id as its Primary Key
The table contains 35 shops
The Primary Key has no gaps (ranging from 1 to 35)

In such cases, the following queries will yield only one row/result:

# Query                          # Output:  # FIELD_1 # FIELD_2 (second statement)
SELECT COUNT(shop.id) FROM shop             # 35
SELECT shop.id, COUNT(shop.id) FROM shop    # 1       # 35

Both aforementioned queries would generate just one result. The first query counts the number of shops (35), while the second one also displays the ID of the first shop (1).

Your specific query operates on the same principle; similar to COUNT, the AVG function is an aggregate function that produces a singular result from the query.

SELECT 
    shop.*, shop.id as shop_id,
    COALESCE(ROUND(AVG(rat.rating),1),0) as avgRate
FROM shop
    LEFT JOIN rating_reviews AS rat ON rat.shop=shop.id

-- Result: All fields from `shop` for the first shop followed by [shop_id] and [avgRate]
-- For instance...
# shop.id # shop.name # shop.type   # shop_id # avgRate
# 1       # Tesco     # Supermarket # 1       # 3.5

Nevertheless, there are two methods to bypass this behavior:

Using GROUP BY

SELECT i, AVG(...) AS a FROM ... LEFT JOIN ... GROUP BY i

Utilizing Nested SELECT statements

SELECT i, (SELECT AVG(...) FROM ... WHERE ...) AS a FROM ...

GROUP BY Clause

SELECT 
    shop.*, shop.id as shop_id,
    COALESCE(ROUND(AVG(rat.rating),1),0) as avgRate
FROM shop
    LEFT JOIN rating_reviews AS rat ON rat.shop=shop.id
GROUP BY shop.id

Nested SELECT Statements

SELECT
    shop.*, shop.id as shop_id,
    (SELECT
         COALESCE(ROUND(AVG(rat.rating),1),0)
     FROM rating_reviews as rat
     WHERE rat.shop=shop.id
    ) as avgRate
FROM shop

Calculating the mean rating for each shop with MySQL

Answer №1

Answer №2

Understanding Aggregate Functions

GROUP BY Clause

Nested SELECT Statements

Similar questions

Is it possible for the client to see the PHP output within this JQuery script?

Using multi-dimensional JSON arrays for posting requests through Ajax

PHP regular expression to extract names of cities from a given string

retrieve PHP function calls as an array using Ajax

Refresh the Dom following an Ajax request (issue with .on input not functioning)

Assistance requested for utilizing stripos() and substr() functions

Dynamic number of parameters in Laravel routes

Problem: Values are not being posted with AJAX when using $(form).serialize()

Unable to obtain Eventbrite API access token using a straightforward request

Discover the executed SQL queries in Laravel

Issue with JSON parsing where quotes are missing around the name field

Using CURL to connect to my personal network at home

I want to utilize a select drop-down menu for navigating between pages in my pagination, breaking away from the traditional method of using <a> tags

Unable to add information using the REST API

What is the process for a server to manage numerous users concurrently?

Transforming COLDFUSION into a powerful tool for encoding a WebServices API HASH instead of PHP code

Retrieving information from a database using PHP PDO to populate a dropdown list

Upgrading outdated pagination mysql_query PHP code to PDO

Eliminate PHP method for removing JSON object name based on index

The behavior of the Google chart seems quite unusual