CODEWITHSUNDEEP
mysqlsqlif-statementnullaverage
Kane Charles
Kane Charles

Reputation: 389

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://www.pawfun.com/wp/wp-content/uploads/2010/01/rabbid.png                                     |
|  4 | Bobby Coon       | https://c2.staticflickr.com/6/5242/5370143072_dee60d0ce2_n.jpg                                     |
|  5 | Doc Raccoon      | http://images.encyclopedia.com/utility/image.aspx?id=2801690&imagetype=Manual&height=300&width=300 |
|  6 | Eddie the Rac    | http://www.felid.org/jpg/EDDIE%20THE%20RACCOON.jpg                                                 |
+----+------------------+----------------------------------------------------------------------------------------------------+
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://www.pawfun.com/wp/wp-content/uploads/2010/01/rabbid.png                                     | 5.0000 |
|  4 | Bobby Coon       | https://c2.staticflickr.com/6/5242/5370143072_dee60d0ce2_n.jpg                                     |   NULL |
|  5 | Doc Raccoon      | http://images.encyclopedia.com/utility/image.aspx?id=2801690&imagetype=Manual&height=300&width=300 | 1.5000 |
|  6 | Eddie the Rac    | http://www.felid.org/jpg/EDDIE%20THE%20RACCOON.jpg                                                 |   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       | https://c2.staticflickr.com/6/5242/5370143072_dee60d0ce2_n.jpg                                     | 0.0000 |
|  6 | Eddie the Rac    | http://www.felid.org/jpg/EDDIE%20THE%20RACCOON.jpg                                                 | 0.0000 |
|  5 | Doc Raccoon      | http://images.encyclopedia.com/utility/image.aspx?id=2801690&imagetype=Manual&height=300&width=300 | 1.5000 |
|  3 | Jesse Coon James | http://www.pawfun.com/wp/wp-content/uploads/2010/01/rabbid.png                                     | 5.0000 |
+----+------------------+----------------------------------------------------------------------------------------------------+--------+

Upvotes: 4

Views: 5713

Answers (3)

John Robbet
John Robbet

Reputation: 76

You can try the following SQL statement with the built-in function COALESCE():

SELECT *,  COALESCE((SELECT AVG(rating) FROM Review WHERE raccoon_id=Raccoon.id GROUP BY raccoon_id), 0) AS "AVG" FROM Raccoon ORDER BY "AVG" ASC;

You can find the manual of this function here .

If you prefer to use IFNULL, you can use

SELECT *, IFNULL((SELECT AVG(rating) FROM Review WHERE raccoon_id=Raccoon.id GROUP BY raccoon_id), 0) AS "AVG" FROM Raccoon ORDER BY "AVG" ASC;

Looks like you misunderstood the scope of the functions.

However, I think a better way is to use left outer join, instead of a sub query, here is the query I wrote for your reference:

select Raccoon.id, Raccoon.name, Raccoon.image_url, IFNULL(AVG(rating),0) avg from Raccoon LEFT OUTER join Review ON raccoon_id =Raccoon.id GROUP BY raccoon.id ORDER BY AVG ASC;

Then you will get the following results: enter image description here

(sorry I don't know how I can post the output of this query, then I took a screenshot :-)

Hope this will help.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You don't want a group by in the subquery. This is dangerous because it could return more than one row (although the where prevents this). More importantly, with no group by, the subquery is an aggregation query that always returns one row. So, you can put the logic in the subquery:

SELECT r.*,
       (SELECT COALESCE(AVG(rev.rating),0)
        FROM Review rev
        WHERE rev.raccoon_id = r.id
       ) AS "AVG"
FROM Raccoon r
ORDER BY "AVG" ASC;

Also: always use qualified column names when you have a correlated subquery. This is a good practice to prevent problems in the future.

Upvotes: 1

John Woo
John Woo

Reputation: 263693

use IFNULL outside your subquery since it will return null it there is not match on the outer table,

IFNULL((SELECT AVG(rating) FROM Review WHERE raccoon_id=Raccoon.id GROUP BY raccoon_id), 0) AS "AVG"

Or you can also use LEFT JOIN,

SELECT  ra.id, ra.name, ra.image_url,
        IFNULL(AVG(rv.rating),0)AS "AVG" 
FROM    Raccoon ra
        LEFT JOIN Review rv
            ON rv.raccoon_id = ra.id
GROUP   BY ra.id, ra.name, ra.image_url  
ORDER   BY "AVG" ASC;

Upvotes: 8

Related Questions