How to use groupby in mysql

Use the GROUP BY syntax in MySQL: SELECT the columns that need to be grouped and calculated. FROM the table that needs grouped data. The WHERE condition is optional to filter the rows to be grouped. GROUP BY groups columns and calculates summary values. Commonly used aggregate functions: SUM (sum), COUNT (count), AVG (average), MIN (minimum value), MAX (maximum value). Grouping restrictions: Only columns involving aggregate functions can be grouped.

How to use GROUP BY in MySQL

Preface

GROUP BY is a SQL statement used to group data in a database and calculate summary values ​​based on the groups. It allows you to group rows in a table based on one or more columns and then calculate summary values ​​for each group using aggregate functions such as SUM, COUNT, and AVG.

Syntax

The basic syntax of the GROUP BY statement is as follows:

SELECT column1, column2, ...
FROM table_name
WHERE condition
GROUP BY column1, column2, ...

Among them:

• column1, column2, ...: Column to group
• table_name: Table to group data from
• condition (optional): Can be used to filter rows to be grouped

Example

Suppose you have a table named "sales" with the following columns:

<code>| product_id | product_name | category | quantity | price |
|:-----------:|:-------------:|:--------:|:--------:|:------:|
| 1           | iPhone        | Electronics | 10      | 1000   |
| 2           | Android phone | Electronics | 15      | 800    |
| 3           | Laptop        | Electronics | 5       | 1200   |
| 4           | Gaming chair  | Furniture   | 8       | 200    |
| 5           | Desk          | Furniture   | 12      | 300    |</code>

To calculate the total quantity and total price for each product category based on the product category, you can use the following GROUP BY statement:

SELECT category, SUM(quantity), SUM(price)
FROM sales
GROUP BY category;

Output:

<code>| category | total_quantity | total_price |
|:--------:|:--------------:|:------------:|
| Electronics | 30      | 3000   |
| Furniture   | 20      | 700    |</code>

Aggregation functions

The GROUP BY statement can be used with the following common aggregate functions:

• SUM: Calculates the sum of the values ​​in a group
• COUNT: Calculate the count of the values ​​in the group
• AVG: Calculate the average of the values ​​in the group
• MIN: Calculate the minimum value of the value in the group
• MAX: Calculate the median value of the group The minimum value of

Grouping Notes

• Only columns involving aggregate functions can be grouped.
• MySQL will generate an error if you group on a column that is not involved in an aggregate function.
• Make sure to use appropriate aggregate functions to prevent incorrect results.
• Consider using the HAVING clause 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!