Workaround for return null for the SUM function in SQL statements

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.