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 following orders and order_items tables in the sample database:

1. Basic Example of Oracle GROUP BY

The following statement uses the GROUP BY clause to find unique order statuses in the orders table:

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 the DISTINCT operator:

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 the COUNT() function to return the quantity of order.

To obtain more meaningful data, you can join the orders table with the customers table 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 use EXTRACT()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 the GROUP BY clause.

4. Oracle GROUP BY with ROLLUP example

The following statement calculates the sales volume by customer_id,## Group them with #status and (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
    );

Execute the above query statement and get the following results-

Recommended tutorial: "

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!