Home » Oracle SQL » ORACLE GROUP BY

ORACLE GROUP BY

GROUP BY clause is used in the SELECT statement to retrieve the result set by group. The query using GROUP BY clause returns only one row per group.

It is often used in conjunction with aggregate functions like AVG, MAX, MIN, COUNT etc.

Syntax

The general syntax of GROUP BY clause is as follows:

SQL

Here,

column1, column2 etc are the columns in our table from which we want to retrieve data.

c_1, c_2 – Columns based on which we want to group data

WHERE – Optional. WHERE clause is used when we further want to specify the metric based on which data is retrieved from the group.

GROUP BY usually comes after FROM when the WHERE condition is not present. In the presence of WHERE clause in the query, GROUP BY comes at the end.

Examples

For the following examples, we will be using Employee and Departments Table from HR schema:

Employee Table from HR Schema

1. Simple GROUP BY Example

Let’s use the following query to find the unique departments in a table by using the GROUP BY clause.

SQL

Result:

Oracle Group BY Example

The query returns all unique departments in a table.

2. GROUP BY with an aggregate function Example

Aggregate function like SUM, MAX, MIN, COUNT etc work on a group of rows and we can use GROUP BY clause to further segregate the results in groups.

SQL

Result:

The query returns the count of all employees in each department.

Oracle Group BY Example with Aggregate Function

In case we want to show the department name in results, then we first need to join the departments and employees table. Let’s see the following query where we use INNER JOIN first.

SQL

Result:

Oracle Group BY Example with Aggregate Function Example 2

The query returns the count of all employees in each department and also shows the department name.

3. GROUP BY with an expression

The following query groups the employees by hire date and returns the number of hires each year.

SQL

Result:

Oracle Group BY Example with Expression

In this query, we used the EXTRACT function to get data about the year from the hire date. This query returns the result grouped by year and the count of employees hired in that year.

4. GROUP BY with WHERE clause

In this query, we use WHERE clause with GROUP BY to find the count of employee in each department and we use inner join to join Employees and Department table for showing the department name in results.

SQL

Result:

Oracle Group BY Example with WHERE

The query returns the count of employee in each department.

GROUP BY with ROLLUP

The following query calculates the total salary and groups them by department_name, salary, and (department_name, salary):

SQL

Result:

Oracle Group BY Example with ROLLUP Function

In this tutorial, we learned how to use GROUP BY in different SQL queries.