How to use group by in mysql

GROUP BY usage in MySQL allows grouping data and calculating aggregate values. The syntax is: Specify the grouping column: GROUP BY group_column_name Apply the aggregate function: aggregate_function(column_name) Return the grouping and aggregation results from the table: SELECT ... FROM table_name

GROUP BY usage in MySQL

As an important keyword for data aggregation in MySQL, GROUP BY allows us to group data in query results and perform further analysis based on the grouping results. calculate.

Syntax

<code class="sql">SELECT aggregate_function(column_name)
FROM table_name
GROUP BY group_column_name;</code>

Parameters

• aggregate_function: Aggregation function, such as SUM, COUNT, AVG, etc.
• column_name: The name of the column to be aggregated
• table_name: The name of the table containing the data to be aggregated
• group_column_name: Column name used for grouping

Usage

1. Group column: GROUP BY The columns specified after the clause are used to group the data. Each unique grouping value creates a separate group.
2. Aggregation function: Aggregation function is applied to the grouped result set to calculate the aggregate value for each group.
3. Aggregation results: The query returns the unique value of the grouping column and the aggregate value.

Example

<code class="sql">SELECT department_id, SUM(salary) AS total_salary
FROM employee
GROUP BY department_id;</code>

This query groups employee data by department and calculates the total salary for each department. The results are as follows:

##101000020150003020000

department_id	total_salary

Notes

• Index: For large data sets, creating indexes on grouping columns can improve query performance.
• Multiple grouping columns: Multiple columns can be used to group data. The syntax is: GROUP BY column_name1, column_name2, ...
• Group null values: Null values ​​are treated as a separate group.
• HAVING clause: HAVING clause is used to filter the aggregate results after grouping. The syntax is: HAVING aggregate_function(column_name) condition

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