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.

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

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:

Department Total_Salary
Support 30000
IT 48000
Sales 42000

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:

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

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:

Department Total_Salary
Support 30000
IT 28000
Sales 32000

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


Tagged , , , , , , , , , , , , . Bookmark the permalink.