How to use groupby in sql

The GROUP BY clause groups the data set by the specified column and aggregates the specified value of each group, syntax: SELECT aggregate_function(column_name) FROM table_name GROUP BY column_name. Its usage includes: 1. Aggregating data (calculating the sum, average, etc. of each group); 2. Grouping data (dividing data into groups by specified columns); 3. Filtering data (combined with the HAVING clause).

GROUP BY syntax

GROUP BY clause sorts the data set by specified columns Groups and aggregates the specified values ​​for each group. The syntax is as follows:

<code>SELECT aggregate_function(column_name)
FROM table_name
GROUP BY column_name</code>

Among them:

• aggregate_function: aggregate function, such as SUM(), COUNT() , AVG(), MAX(), MIN() etc.
• column_name: Column used for grouping.

Usage

The main usage of GROUP BY includes:

• Aggregate data: Calculate the aggregate value (sum, average, maximum, minimum, etc.) of each group.
• Group data: Divide the data into groups by specified columns to facilitate analysis of the data in each group.
• Filtering data: Combined with the HAVING clause, filter the grouped data.

Example

Suppose we have a table named Sales with the following data:

OrderID	Product	Category	Price
1	Product A	Category 1	10
2	Product A	Category 1	15
3	Product B	Category 2	20
4	Product C	Category 3	30

Example 1: Calculate each category Total price of the product

SELECT Category, SUM(Price) AS TotalPrice
FROM Sales
GROUP BY Category;

Result:

Category	TotalPrice
Category 1	25
Category 2	20
Category 3	30

Example 2: Group by product, count the number of orders

SELECT Product, COUNT(*) AS OrderCount
FROM Sales
GROUP BY Product;

Result:

##Product A2Product B1Product C1

Product	OrderCount

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