What is mysql sum function

There are two mysql summation functions: 1. The count() function is used to count the number of rows in the query results; 2. The sum() function is used to calculate the value of all rows in a certain field. and.

The operating environment of this article: windows7 system, mysql version 5.7, Dell G3 computer.

What is mysql sum function?

count() function and sum() function

Their usage and difference:

1. mysql The count() function in the database counts the number of rows in the query results. For example, we have the following table user_auth:

Use the count() function to query the number of results. Use the following query:

mysql > select count(*) from user_auth ;

Note that count will ignore NULL results, so if count(field name) is used like this, if If the field contains null results, the query results will be inaccurate. We change the data in the table as follows:

At this time, query: mysql > select count (user_id) from user_auth; The result is as follows:

2. The sum() function in mysql is used for calculation The sum of the values ​​of all rows in a certain field (sum will filter null when summing, and will not be calculated), for example, the following query:

mysql > select sum(user_id) from user_auth; The result is as follows:

In addition, you can also use sum(condition) to match the conditions Sum the number of result rows and query as follows:

mysql > select sum(user_id is null),sum(aid = 1) from user_auth; The result is as follows:

It should be noted that if the sum() function is used in a row without a return, the return value of the sum function is null, not 0, example:

mysql > select sum(user_id) from user_auth where id not in (2,4,6 ); The results are as follows:

At this time, an error will be reported when using JDBC or some third-party frameworks for mapping. The solution is: use IFNULL(sum(user_id),0) or COALESCE(sum(user_id),0) convert the result.

[Related recommendations: mysql video tutorial]

The above is the detailed content of What is mysql sum function. For more information, please follow other related articles on the PHP Chinese website!