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 an optional clause of the SELECT statement. The GROUP BY clause allows you to group rows into a set of summary rows by values of columns or expressions and return one row for each group.

Here’s the basic syntax of the GROUP BY clause:

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

Note that the SELECT statement may include other clauses such as where, joins and fetch.

Suppose you have the following orders table:

Note that the orders table in the sample database has more rows than this table.

The following statement uses the GROUP BY clause to group the rows in the orders table into groups:

SELECT
  status
FROM
  orders
GROUP BY
  status;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Since the GROUP BY clause groups the orders by their statuses, it returns the following intermediate result set that has three groups:

• Pending: 1 row
• Shipped: 3 rows
• Canceled: 1 row

Then the SELECT clause retrieves the status column and returns the following result set:

The result indicates that the GROUP BY clause creates fewer rows from the initial result set.

In practice, you’ll use the GROUP BY clause with aggregate functions.

An aggregate function returns a single result for a list of values. For example, you can use the COUNT() aggregate function to return the number of items.

The following shows how to count the number of orders by status:

SELECT
  status,
  COUNT(order_id) order_count
FROM
  orders
GROUP BY
  status;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The query returns the following intermediate result set:

In this result set, the COUNT function returns 1 for the pending orders, 3 for the shipped orders, and 1 for the canceled orders.

Then the SELECT clause returns a result set that includes the status and order_count columns:

Besides the COUNT function, Oracle provides other standard aggregate functions such as AVG(), MAX(), MIN() and SUM().

When executing a SELECT statement, Oracle evaluates the GROUP BY clause after the WHERE clause and before the HAVING clause in the following sequence:

• FROM
• WHERE
• GROUP BY
• HAVING
• SELECT
• DISTINCT
• ORDER BY

Oracle GROUP BY clause examples #

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

Basic Oracle GROUP BY clause 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)

Try it

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

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

Try it

Oracle GROUP BY with an aggregate function example #

The following statement uses a GROUP BY clause with the COUNT function to return 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)

Try it

In this example, we group the orders by customers and use 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)

Try it

Here is the result:

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) order_year,
    COUNT( order_id ) order_count
FROM
    orders
GROUP BY
    EXTRACT(YEAR FROM order_date)
ORDER BY
    order_year;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

How it works:

• First, group orders by years using the GROUP BY clause.
• Second, extract the year from the order date using the EXTRACT() function.
• Third, count the order id by years using the COUNT function.

Note that Oracle evaluates the GROUP BY clause before the SELECT clause, you cannot use the order_year alias in the GROUP BY clause. Therefore, you need to use the expression EXTRACT(year FROM order_date) twice, one in the GROUP BY clause and one in the SELECT clause.

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)

Try it

Output:

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

Summary #

• Use Oracle GROUP BY clause to group rows into groups.
• Use the GROUP BY clause with an aggregate function to calculate a value for each group.

Quiz #