Search code examples
mysqlaverage

Mysql make AVG return null if any value is null

I'm trying to take an AVG from one of my columns in MySQL. However, the calculation must be done in another way if some of the values are null. So, I'm trying to make this:

I want SELECT AVG(amount) from sales to return NULL if any row in the sales table has amount NULL. Can this be done?

Solution

  • I would try with (it counts nulls as zeros):

    SELECT AVG(IFNULL(amount,0)) from sales

    Edited (misinterpreted the question before):

    SELECT IF(sum(amount is NULL), NULL, AVG(amount)) from sales