dimanche 29 novembre 2015

MySQL AVG() return 0 if NULL

I have 3 tables, shown below:

mysql> select * from Raccoon;
+----+------------------+----------------------------------------------------------------------------------------------------+
| id | name             | image_url                                                                                          |
+----+------------------+----------------------------------------------------------------------------------------------------+
|  3 | Jesse Coon James | http://ift.tt/1l16r4V                                     |
|  4 | Bobby Coon       | http://ift.tt/1ItwKWi                                     |
|  5 | Doc Raccoon      | http://ift.tt/1l16ttM |
|  6 | Eddie the Rac    | http://ift.tt/1ItwM0q                                                 |
+----+------------------+----------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

mysql> select * from Review;
+----+------------+-------------+---------------------------------------------+--------+
| id | raccoon_id | reviewer_id | review                                      | rating |
+----+------------+-------------+---------------------------------------------+--------+
|  1 |          3 |           1 | This raccoon was a fine raccoon indeed.     |      5 |
|  2 |          5 |           2 | This raccoon did not do much for me at all. |      2 |
|  3 |          3 |           1 | asdfsadfsadf                                |      5 |
|  4 |          5 |           2 | asdfsadf                                    |      1 |
+----+------------+-------------+---------------------------------------------+--------+
4 rows in set (0.00 sec)

mysql> select * from Reviewer;
+----+---------------+
| id | reviewer_name |
+----+---------------+
|  1 | Kane Charles  |
|  2 | Cameron Foale |
+----+---------------+
2 rows in set (0.00 sec)

I'm trying to build a select query that will return all of the columns in Raccoon as well as an extra column which grabs an average of Review.rating (grouped by id). The problem I face is that there is no guarantee that there will be rows present in the Review table for every single Raccoon (as determined by the FK, raccoon_id which references Raccoon.id. In situations where there are zero rows present in the Review table (for a given Raccoon.id, ie Review.raccoon_id) I'd like the query to return 0 as the average for that Raccoon.

Below is the current query I'm using:

mysql> SELECT *, (SELECT IFNULL(AVG(rating),0) FROM Review WHERE raccoon_id=Raccoon.id GROUP BY raccoon_id) AS "AVG" FROM Raccoon ORDER BY "AVG" ASC;
+----+------------------+----------------------------------------------------------------------------------------------------+--------+
| id | name             | image_url                                                                                          | AVG    |
+----+------------------+----------------------------------------------------------------------------------------------------+--------+
|  3 | Jesse Coon James | http://ift.tt/1l16r4V                                     | 5.0000 |
|  4 | Bobby Coon       | http://ift.tt/1ItwKWi                                     |   NULL |
|  5 | Doc Raccoon      | http://ift.tt/1l16ttM | 1.5000 |
|  6 | Eddie the Rac    | http://ift.tt/1ItwM0q                                                 |   NULL |
+----+------------------+----------------------------------------------------------------------------------------------------+--------+
4 rows in set (0.00 sec)

As you can see above, the query isn't returning 0 for Raccoons with id of 4 and 6, it is simply returning NULL. I need it to return something like the following (note the ordering, sorted by lowest average review first):

+----+------------------+----------------------------------------------------------------------------------------------------+--------+
| id | name             | image_url                                                                                          | AVG    |
+----+------------------+----------------------------------------------------------------------------------------------------+--------+
|  4 | Bobby Coon       | http://ift.tt/1ItwKWi                                     | 0.0000 |
|  6 | Eddie the Rac    | http://ift.tt/1ItwM0q                                                 | 0.0000 |
|  5 | Doc Raccoon      | http://ift.tt/1l16ttM | 1.5000 |
|  3 | Jesse Coon James | http://ift.tt/1l16r4V                                     | 5.0000 |
+----+------------------+----------------------------------------------------------------------------------------------------+--------+

Aucun commentaire:

Enregistrer un commentaire