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:

Example 1: Calculate each category Total price of the product

<code class="sql">SELECT Category, SUM(Price) AS TotalPrice
FROM Sales
GROUP BY Category;</code>

Result:

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

<code class="sql">SELECT Product, COUNT(*) AS OrderCount
FROM Sales
GROUP BY Product;</code>

Result:

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!