Shulou(Shulou.com)06/01 Report--

This article introduces the knowledge about how to use the sum () function to return null errors in Mysql. In the operation of actual cases, many people will encounter such a dilemma. Then let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Introduction

The SUM () function is used to calculate the sum of a set of values or expressions. The syntax of the SUM () function is as follows:

SUM (DISTINCT expression)

How does the SUM () function work?

If you use the SUM function in a SELECT statement that does not return a matching row, the SUM function returns NULL instead of 0.

The DISTINCT operator allows you to evaluate different values in the collection.

The SUM function ignores the null value in the calculation.

problem

When you use the sum () function in Mysql for summation in a project, the following error message is reported during debugging:

Sql: Scan error on column index 0, name "total": converting NULL to int64 is unsupported

The sql statement I executed accordingly is as follows:

SELECT SUM (consume) as total FROM task_income WHERE consume_type=3 AND uid=20; authentication

To find out why, I ran the sql and parameters that went wrong in Navicat and found that null was indeed returned in the result:

According to what I thought when I was developing:

If you have the corresponding data, return the summed value

If there is no eligible data, return 0

As a result, null is returned when there is no eligible data.

Solve

This can be solved by using a combination of ISNULL or COALESCE functions.

The corresponding examples are as follows:

SELECT IFNULL (SUM (Column1), 0) AS total FROM...SELECT COALESCE (SUM (Column1), 0) AS total FROM... Difference

As mentioned above, the problem of null can be solved by using either the ISNULL or COALESCE functions, so what's the difference between the two functions?

The ISNULL function takes two arguments. If the first parameter is not null, it returns directly. If the first parameter is null, the second parameter is returned.

The COALESCE function can take two or more parameters and return the first parameter that is not null. If all parameters are null, it returns null.

This is the end of the content of "how to solve the null error report using the sum () function in Mysql". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.