How to write grouping function in mysql

The grouping function in MySQL is used to calculate aggregate values ​​by grouping the data set. Commonly used functions are: SUM: Calculate the sum of the values ​​in the specified column COUNT: Calculate the number of non-NULL values ​​in the specified column AVG: Calculate the average value of the values ​​in the specified column MIN: Calculate the minimum value in the specified column MAX: Calculate the number of non-NULL values ​​in the specified column The maximum value of

MySQL grouping function

The grouping function in MySQL is used to group based on one or more groups of columns Group the dataset and calculate aggregate values ​​(e.g. SUM, COUNT, AVG) for each group. The following is the syntax of commonly used grouping functions:

SELECT 列1, 列2, 聚合函数(列3)
FROM 表名
GROUP BY 列1, 列2

Commonly used grouping functions

• SUM (column name): Calculate the value in the specified column Sum.
• COUNT(column name): Counts the number of non-NULL values ​​in the specified column.
• AVG(column name): Calculate the average of the values ​​in the specified column.
• MIN(column name): Calculate the minimum value in the specified column.
• MAX(column name): Calculate the maximum value in the specified column.

Group function usage example

The following example demonstrates how to use the group function to calculate the total sales of each product in the sales record:

SELECT product_id, SUM(quantity_sold) AS total_sales
FROM sales_records
GROUP BY product_id;

The results will show the product_id and total sales of each product.

Note

• The grouping function can only be used on the columns specified in the GROUP BY clause.
• If multiple columns are specified in the GROUP BY clause, the aggregate function can be applied only to those columns or a subset of their expressions.
• Group functions cannot be used for subqueries.
• When using grouping functions, you need to pay attention to how NULL values ​​are handled. If a NULL value exists in the specified column, that value is excluded from the calculation.

The above is the detailed content of How to write grouping function in mysql. For more information, please follow other related articles on the PHP Chinese website!