Detailed explanation of sum usage in mysql

SUM() function calculates the sum of non-null values ​​in the specified column. It supports DISTINCT, filters, partial sums, and use with other aggregate functions. SUM() ignores NULL values, returns negative values, and returns NULL for non-numeric values.

SUM() function in MySQL

SUM() function introduction

SUM() function is used to calculate the sum of all non-NULL values ​​in the specified column. It is an aggregate function that operates on a set of rows and returns a single value.

Syntax

<code class="sql">SUM(column_name)</code>

Where, column_name is the target column for which the sum is to be calculated.

Usage Example

The following is an example of using the SUM() function to calculate the sum of the sales columns in the table:

<code class="sql">SELECT SUM(sales)
FROM sales_table;</code>

NULL value handling

The SUM() function ignores NULL values. This means it only performs calculations on non-empty cells.

Distinct Keyword

You can use the DISTINCT keyword to exclude duplicate values. For example, the following query calculates the total sales of different customers in the table:

<code class="sql">SELECT SUM(DISTINCT sales)
FROM sales_table;</code>

Filter conditions

can be used in the SUM() functionWHERE clause to apply filter conditions. For example, the following query calculates the total sales of greater than $100 in the table:

<code class="sql">SELECT SUM(sales)
FROM sales_table
WHERE sales > 100;</code>

PARTS AND

You can use PARTITION BY and ORDER The BY clause groups the results and calculates partial sums. For example, the following query calculates the total sales for each customer, sorted by date:

<code class="sql">SELECT customer_id, SUM(sales)
FROM sales_table
GROUP BY customer_id
ORDER BY date;</code>

Other Notes

• The SUM() function can be combined with other aggregations Used together with functions such as COUNT() and AVG().
• If the target column contains negative values, the SUM() function returns negative values.
• The SUM() function returns NULL if the target column contains non-numeric values.

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