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:

SELECT column_1, column_2....
FROM table_name

[ WHERE condition ]

GROUP BY c_1,c_2...;

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:

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.

SELECT department_id
FROM employees
GROUP BY department_id;

Result:

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.

SELECT department_id, COUNT(employee_id)
FROM employees
GROUP BY department_id
ORDER BY department_id;

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.

SELECT department_name, COUNT(employee_id)
FROM employees
INNER JOIN departments
USING(department_id)
GROUP BY department_name
ORDER BY department_name;

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.

SELECT
EXTRACT(YEAR FROM hire_date) YEAR,
COUNT( employee_id )
FROM
employees
GROUP BY
EXTRACT(YEAR FROM hire_date)
ORDER BY
YEAR;

Result:

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.

SELECT department_name, COUNT( employee_id )
FROM employees
INNER JOIN departments USING(department_id)
WHERE
salary > 5000
GROUP BY
department_name
ORDER BY
department_name;

Result:

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):

SELECT
    department_name,
    salary,
    SUM( salary + salary * NVL((commission_pct),0) ) "Total Salary"
FROM
    employees
INNER JOIN departments USING(department_id)
GROUP BY
    ROLLUP(department_name, salary );

Result: