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