How to use groupby in mysql

GROUP BY is used to group and summarize the data in the table by specified columns, and use aggregate functions (such as SUM, COUNT, AVG) to make statistics on the data in the group. The syntax is SELECT aggregate_function(column_name) AS alias FROM table_name GROUP BY column_name.

GROUP BY clause: Grouping data in MySQL

What is GROUP BY?

The GROUP BY clause is used to group data in a table according to specified columns. After grouping, it performs aggregate functions such as SUM, COUNT, AVG, etc. on each group to summarize and count the data within the group.

Syntax:

SELECT aggregate_function(column_name) AS alias
FROM table_name
GROUP BY column_name

Parameters:

• ##aggregate_function(): To be executed Aggregation functions (for example, SUM, COUNT, AVG).
• column_name: Specify the column name to group by.
• alias: Alias ​​of the aggregated result (optional).

Working principle:

The GROUP BY clause works through the following steps:

Put the data in the table according to the specified Column grouping.
1. Execute the aggregate function on each group to produce summary values.
2. Return the summary value of the group together with the column name by which it is grouped.

Example:

The following example uses the GROUP BY clause to find the number of employees in each department:

SELECT department_id, COUNT(*) AS num_employees
FROM employees
GROUP BY department_id;

The results will show the departments ID and number of employees per department.

Note:

The columns to be grouped by must be included in the SELECT clause.
• For each grouping, only one aggregate function can be executed.
• If you do not specify an alias, the aggregate result will use the name of the aggregate function.
• HAVING clause can be used to further filter the grouped results.

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