GROUP BY Clause in Oracle SQL – PLSQL

The GROUP BY clause in Oracle SQL / PLSQL is used in SELECT statement and allows us to filter the records by grouping them as per one or more columns.

Syntax for the GROUP BY clause in Oracle SQL / PLSQL is:

SELECT column_name1
,column_name2
,column_name3
.
.
,column_nameN
Aggregate_function(value/expression)
FROM table_name
WHERE conditions
GROUP BY column_name1
,column_name2
.
.
column_nameN;

The aggregate function can be SUM(), MIN(), MAX() or COUNT().

Let’s take an example for understanding:

Suppose have a table named ‘employee’ in the database as shown below.

Scenario 1:

Suppose we want to see how much ‘Salary’ is being given to which department.

We can achieve the same as:

SELECT department
       ,SUM(Salary) Total_Salary
FROM employee
GROUP BY department;

The result of the above query will be:

Here we have retrieved the records explaining us that ‘30000’ in total is being given to employee(s) working in the ‘Sales’ department, ‘48000’ in total is being given to employee(s) working in the ‘IT’ department and ‘42000’ is being given to employee(s) working in the ‘Sales’ department using GROUP BY clause in SELECT statement.

Also, note that ‘Total_Salary’ after the aggregate function ‘SUM(salary) ‘ acts as an alias name for the column in query result.

Scenario 2:

Suppose we want to see the maximum ‘Salary’ being given in every department
Let’s assume that we have a new table namely ‘employee’ as shown below:

We can achieve the same as:

SELECT department
       ,MAX(salary) Maximum_Salary
FROM employee
GROUP BY department;

Once we have run the above code following will be the result:

Here we have successfully retrieved the records pertaining to maximum salary being given in each department using GROUP BY clause in SELECT statement.