Which is the mysql summation function?

This article mainly shows you the "mysql summation function is which", the content is easy to understand, clear, hope to help you solve doubts, the following let the editor lead you to study and learn "mysql summation function is which" this article.

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

This article operating environment: windows7 system, mysql version 5.7, Dell G3 computer.

What is the mysql summation function?

Count () function and sum () function

Their usage and differences:

1. The count () function in the mysql database is used to count 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, using the following query:

Mysql > select count (*) from user_auth

Note that count ignores the result of NULL, so if count (field name) is used in this way, if the field contains the result of null, the query result will be inaccurate. We change the data in the table as follows:

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

2. The sum () function in mysql is used to calculate the sum of the values of all rows in a field (null is filtered when sum is summed, but not calculated), such as the following query:

Mysql > select sum (user_id) from user_auth; results are as follows:

You can also use sum (condition) to sum the number of resulting rows that meet the criteria, as shown in the following query:

Mysql > select sum (user_id is null), sum (aid = 1) from user_auth; results are as follows:

It is important to note that if you use the sum () function in a row that does not return, the return value of the sum function is null, not 0, for example:

Mysql > select sum (user_id) from user_auth where id not in (2d4). The results are as follows:

At this point, an error will be reported when mapping using JDBC or some third-party framework, and the solution is to transform the result using IFNULL (sum (user_id), 0) or COALESCE (sum (user_id), 0).

The above is all the content of the article "which is the mysql summation function?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!