How to use oracle group by

In Oracle, the "group by" keyword needs to be used together with the SELECT statement to group query results. Rows can be combined into grouped summary rows by row or expression value; syntax "SELECT field list FROM data table name GROUP BY field name;".

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

Introduction to Oracle GROUP BY clause

The GROUP BY keyword can group query results based on one or more fields.

The GROUP BY clause is used in the SELECT statement to combine rows into grouped summary rows by the value of the row or expression. The GROUP BY clause returns one row for each group.

The GROUP BY clause is typically used with aggregate functions such as AVG(), COUNT(), MAX(), MIN(), and SUM(). In this case, the aggregate function returns summary information for each grouping. For example, given several categories of products, the AVG() function returns the average price of the products in each category.

The following illustrates the syntax of the Oracle GROUP BY clause:

SELECT 字段列表 FROM 数据表名 GROUP BY 字段名;

The GROUP BY clause appears after the FROM clause. When a WHERE clause is provided, the GROUP BY clause must be placed before the WHERE clause.

The GROUP BY clause groups rows by the values in the grouping column (such as c1, c2 and c3). The GROUP BY clause can only contain aggregated or grouped columns.

If you want to specify multiple grouping levels that should be calculated at once, use the following ROLLUP syntax:

SELECT 字段列表 FROM 数据表名 GROUP BY ROLLUP(c1,c2,c3);

Oracle GROUP BY Example

We will demonstrate using the followingordersandorder_itemstables in the sample database:

1. Basic Example of Oracle GROUP BY

The following statement uses theGROUP BYclause to find unique order statuses in theorderstable:

SELECT status FROM orders GROUP BY status;

Execute the above query statement and get the following results-

This statement has the same effect as the following statement using theDISTINCToperator:

SELECT DISTINCT status FROM orders;

2. Oracle GROUP BY aggregation example

The following statement returns the customer’s order quantity:

SELECT customer_id, COUNT( order_id ) FROM orders GROUP BY customer_id ORDER BY customer_id;

Execute the above query statement , and get the following results -

In this example, we group the orders by customer number and use theCOUNT()function to return the quantity of order.

To obtain more meaningful data, you can join theorderstable with thecustomerstable as follows:

SELECT name, COUNT( order_id ) FROM orders INNER JOIN customers USING(customer_id) GROUP BY name ORDER BY name;

Execute the above query statement , get the following results-

3. Oracle GROUP BY expression example

The following example is by year Groups the orders and returns the number of orders per year. Refer to the following query statement-

SELECT EXTRACT(YEAR FROM order_date) YEAR, COUNT( order_id ) FROM orders GROUP BY EXTRACT(YEAR FROM order_date) ORDER BY YEAR;

Execute the above query statement and get the following results-

In this example, we useEXTRACT()The function gets the year information from the date of the order.

Unlike the previous example, this uses an expression that returns the year in theGROUP BYclause.

4. Oracle GROUP BY with ROLLUP example

The following statement calculates the sales volume bycustomer_id,## Group them with #statusand (customer_id,status):

SELECT customer_id, status, SUM( quantity * unit_price ) sales FROM orders INNER JOIN order_items USING(order_id) GROUP BY ROLLUP( customer_id, status );

Oracle Tutorial"

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