syntax and example of group by clause in oracle sql

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.