Simply explore the SUM () function to return NULL

The SUM () function of the mysql is used to find a sum of a field of all records. The following article introduces you the question about MySQL using the SUM () function returns NULL, and the document is introduced in very detailed, Friends you need can refer to

introduce

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 (DistINCTexpression)

How is the SUM () function work?

If the SUM function is used in the SUM function without returning the match, the SUM function returns NULL instead of 0.

The Distinct operator allows different values ??in the set.

The SUM function ignores the NULL value in the calculation.

question

When using the SUM () function in MySQL in the project, the following error message is found when debugging:

SQL: ScaneRroncolumnIndex0, Name “Total”: ConvertingnullToint64isunsupported

The SQL statement I executed accordingly is as follows:

SELECTSUM (Consume) AStotAlMtask_IncomeWhereConsume_type = 3anduid = 20;

verify

In order to find the reason, I executed the SQL and parameters of the problem over the Navicat, and the results are indeed returned NULL:

According to my previous idea:

If there is a corresponding data, return and afterwards

If there is no qualified data, return 0

As a result, NULL is returned when there is no conditional data.

solve

You can solve it by combining the use of Isnull or Coalesce functions.

The corresponding example is as follows:

Selectifnull (SUM (Column1), 0) ASTOALFROM … SELECTCOALESCE (SUM (Column1), 0) AstotaRFROM …

the difference

Said above, using the isnull or coalesce function can solve the problem with null, then what is the difference between these two functions?

The isnull function accepts two parameters. If the first parameter is not NULL, it will return directly. If the first parameter is null, return a second parameter.

The coalesce function can accept two or more parameters and return the first non-NULL parameter, and if all parameters are null, return NULL

refer to

HowDoiiGeTSumFunctionInmysqltoreTurn’0’ifnovAluesareFound? -Stackoverflowsql-whatisthedifferenceBewteenifnullandcoalesceinMysql? -Stackoverflow

Here, this article about mysql uses the sum () function to return null problems.

Originally from: