Oracle GROUP BY

Summary: in this tutorial, you will learn how to use the Oracle GROUP BY clause to group rows into groups.

Introduction to Oracle GROUP BY clause

The GROUP BY clause is used in a SELECT statement to group rows into a set of summary rows by values of columns or expressions. The GROUP BY clause returns one row per group.

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

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

SELECT column_list FROM T GROUP BY c1,c2,c3;
Code language: SQL (Structured Query Language) (sql)

The GROUP BY clause appears after the FROM clause. In case WHERE clause is presented, the GROUP BY clause must be placed after the WHERE clause as shown in the following query:

SELECT column_list FROM T WHERE condition GROUP BY c1, c2, c3;
Code language: SQL (Structured Query Language) (sql)

The GROUP BY clause groups rows by values in the grouping columns such as c1, c2 and c3. The GROUP BY clause must contain only aggregates or grouping columns.

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

SELECT column_list FROM T GROUP BY ROLLUP(c1,c2,c3);
Code language: SQL (Structured Query Language) (sql)

Please check it out the ROLLUP tutorial for the more information.

Oracle GROUP BY examples

We will use the following orders and order_items in the sample database for the demonstration:

Oracle GROUP BY - Orders & Order_items table

A) Oracle GROUP BY basic example

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

SELECT status FROM orders GROUP BY status;
Code language: SQL (Structured Query Language) (sql)
Oracle GROUP BY - group single column

This statement has the same effect as the following statement that uses the DISTINCT operator:

SELECT DISTINCT status FROM orders;
Code language: SQL (Structured Query Language) (sql)

B) Oracle GROUP BY with an aggregate function example

The following statement returns the number of orders by customers:

SELECT customer_id, COUNT( order_id ) FROM orders GROUP BY customer_id ORDER BY customer_id;
Code language: SQL (Structured Query Language) (sql)
Oracle GROUP BY - with aggregate function

In this example, we grouped the orders by customers and used the COUNT() function to return the number of orders per group.

To get 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;
Code language: SQL (Structured Query Language) (sql)

Here is the result:

Oracle GROUP BY - with join example

C) Oracle GROUP BY with an expression example

The following example groups the orders by year and returns the number of orders per year.

SELECT EXTRACT(YEAR FROM order_date) YEAR, COUNT( order_id ) FROM orders GROUP BY EXTRACT(YEAR FROM order_date) ORDER BY YEAR;
Code language: SQL (Structured Query Language) (sql)

In this example, we used the EXTRACT() function to get the year information from the order’s dates.

Unlike the previous examples, we used an expression that returns the year in the GROUP BY clause.

The following picture illustrates the result:

Oracle GROUP BY - expression example

D) Oracle GROUP BY with WHERE clause example

This example uses the GROUP BY clause with a WHERE clause to return the number of shipped orders for every customer:

SELECT name, COUNT( order_id ) FROM orders INNER JOIN customers USING(customer_id) WHERE status = 'Shipped' GROUP BY name ORDER BY name;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

oracle group by with where clause example

Note that the Oracle always evaluates the condition in the WHERE clause before the GROUP BY clause.

E) Oracle GROUP BY with ROLLUP example

The following statement computes the sales amount and groups them by customer_id, 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 );
Code language: SQL (Structured Query Language) (sql)
Oracle GROUP BY ROLLUP example

In this tutorial, you have learned how to use the Oracle GROUP BY clause to group rows into groups.

Was this tutorial helpful?