Workaround for return null for the SUM function in SQL statements

Sum is the standard SUM function in an SQL statement, and the SUM function returns null if there are no records that match the criteria.

But in most cases, we want it to return 0 instead of NULL if the condition record is not met, so we can use the following method, for example:

SELECT COALESCE (SUM (name), 0) from the person WHERE ID > 0

Okay, so you don't have to bother with the case of whether the return result is null.

The COALESCE function means returning the first empty value in the argument list, which allows multiple arguments to be passed in, which is also a standard function in SQL.

And then looked up the judgment about the null value. Address: http://www.w3schools.com/sql/sql_isnull.asp

SQL Server/ms Access

1 SELECT productname,unitprice*(UnitsInStock+ISNULL(UnitsOnOrder,0))
2 from Products

Oracle

Oracle does not has an ISNULL () function. However, we can use the NVL () function to achieve the same result:

1 SELECT productname,unitprice*(UnitsInStock+NVL (UnitsOnOrder,0))
2 from Products

Mysql

MySQL does has an ISNULL () function. However, it works a little bit different from Microsoft ' s ISNULL () function.

In MySQL we can use the Ifnull () function as this:

1 SELECT productname,unitprice*(UnitsInStock+ifnull (UnitsOnOrder,0))
2 from Products

Or we can use the coalesce () function as this:

1 select ProductName, Unitprice* (UnitsInStock +coalesce ( Unitsonorder,0 2  from products