How to use group by in oracle

The GROUP BY statement in Oracle is used to group data and aggregate summary values ​​according to specified columns. The syntax is: SELECT aggregate function (column name), grouping column FROM table name GROUP BY grouping column. Features include grouping data with the same grouping column value, applying an aggregate function to each group to calculate a summary value, the grouping column is used to group the data, and the aggregate function is used to calculate the summary value.

Usage of GROUP BY in Oracle

GROUP BY is used in Oracle to group data according to specified columns and aggregated SQL statements.

Syntax

<code>SELECT 聚合函数(列名), 分组列
FROM 表名
GROUP BY 分组列</code>

Function

• Group data with the same grouping column value together.
• Apply an aggregate function (such as SUM, COUNT, AVG) to each group to calculate summary values.

Group column

Group column is a column used to group data. You can group multiple columns at once.

Aggregation function

The aggregation function calculates the summary value for each group. Oracle supports a variety of aggregate functions, including:

• SUM
• COUNT
• AVG
• MAX
• MIN

Example

Query the total order quantity of a customer:

<code>SELECT COUNT(order_id), customer_id
FROM orders
GROUP BY customer_id;</code>

Query the average number of orders for each product:

<code>SELECT AVG(order_quantity), product_id
FROM order_details
GROUP BY product_id;</code>

Note:

• The grouping column must appear in the SELECT clause.
• Aggregation functions must appear in the SELECT clause with the grouping column.
• NULL values ​​do not affect grouping and are treated as a separate group.

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