«Back to Home

Oracle Jump Start
Topics

How To Use Group By Clause In OraclePLSQL

Description
 
Group By clause is used in a select statement. Group by clause collects all the data, multiple records and group a result into one or more columns. Group by clause can only be used with aggregate function like Sum, Count, Max, Min and Avg.
 
Syntax
  1. Select  
  2. Expression Aggregate function()  
  3. from  
  4. table_name  
  5. where  
  6. condition  
  7. Group by  
  8. expression  
Example
 
The example, given below, uses Sum function-
  1. select  
  2. Emp_name,  
  3. Sum(salary) As "Total salary"  
  4. From  
  5. Employee  
  6. group by  
  7. Emp_name;  
1
 
Example
 
The example, given below, uses Count function-
 
  1. select  
  2. Emp_name,  
  3. count(*) As "Total salary"  
  4. from  
  5. Employee  
  6. where  
  7. salary > 20000  
  8. group by  
  9. Emp_name;  
2
 
Example
 
The example, given below, uses Min function-
 
  1. select  
  2. Emp_name,  
  3. Min(salary) As "Lowest Salary"  
  4. from  
  5. Employee  
  6. Group By  
  7. Emp_name;  
3
 
Example
 
The example, given below, uses Max function-
 
  1. select  
  2. Emp_name,  
  3. Max(salary) As "Lowest Salary"  
  4. from  
  5. Employee  
  6. Group By  
  7. Emp_name;  
4 
 
Example
 
The example, given below, uses AVG function-
 
  1. select  
  2. Emp_name,  
  3. Max(salary) As "Lowest Salary"  
  4. from  
  5. Employee  
  6. Group By  
  7. Emp_name;  
5
 
Summary
 
Thus, we learnt, Group By Clause is used in a select statement. Group by clause can only be used with an aggregate function like Sum, Count, Max, Min and Avg. We learnt, how to use this clause in Oracle with the examples.