Use IFNULL to replace NULL values in your data with a more suitable or meaningful value. This can be helpful when you want to avoid displaying NULLs in your charts or when you need a specific value for further calculations.
Sample usage
IFNULL(Discount, 0)
Syntax
IFNULL( input_expression, null_result )
Parameters
input_expression-- The expression to evaluate. You can use any valid expression as theinput_expression.
null_result-- The value to return ifinput_expressionis null. Ifinput_expressionis not null,IFNULLreturns theinput_expression. You can use any valid expression as thenull_result.
Example
Calculate the average discount given to your customers, including orders that received no discount. However, your application sometimes represents "no discount" as null rather than 0.
The formula AVG(Discount) won't include null discounts. You'll need to convert those nulls to zero. To do this, create a new field called, for example, Discount No Nulls:
IFNULL(Discount, 0)
This formula can be read, "If the Discount field is null, return 0, otherwise return Discount."
You can then calculate the average discount including orders with no discount:
AVG(Discount No Nulls)