Home > Database > SQL > How to use groupby in sql

How to use groupby in sql

下次还敢
Release: 2024-05-01 23:18:36
Original
1361 people have browsed it

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).

How to use groupby in sql

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>
Copy after login

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

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

Result:

Category TotalPrice
Category 1 25
Category 2 20
Category 3 30

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

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

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!

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template