How to group query in oracle

In Oracle, you can use the "group by" keyword with an aggregate function to implement grouping queries. This statement can group the result set according to one or more columns. The syntax is "select field name, aggregate function from table name group by field name".

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

Oracle how to group query

Group query

Group, classify things according to certain aspects of the same nature. That is, within the same group of things, certain aspects of properties are consistent; between different groups, they are different in specified properties. After grouping by a specified property, a group containing any number of objects will be identified by that property and presented as a record as a whole.

Group by

The GROUP BY statement is used in conjunction with aggregate functions to group the result set based on one or more columns.

Perform group statistics and use aggregation functions to count the accumulation (SUM), average (AVG), maximum value (MAX), minimum value (MIN), etc. of certain properties within the group

AVG Returns the average value in the specified group, null values ​​are ignored.

Example:

select prd_no,avg(qty) from sales group by prd_no

COUNT returns the number of items in the specified group.

Example:

select count(prd_no) from sales

MAX returns the maximum value of the specified data.

Example:

select prd_no,max(qty) from sales group by prd_no

MIN returns the minimum value of the specified data.

Example:

select prd_no,min(qty) from sales group by prd_no

SUM returns the sum of the specified data. It can only be used for numeric columns. NULL values ​​are ignored.

Example:

select prd_no,sum(qty) from sales group by prd_no

Explanation:

Items 1, 3, 4, and 5 are grouped according to prd_no and return two columns of data, prd_no and its corresponding statistical value .

Item 2 counts the number of records of "prd_no=specified value" in the sales table.

Recommended tutorial: "Oracle Video Tutorial"

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