Oracle GROUP BY

Education is not limited to just classrooms. It can be gained anytime, anywhere... - Ravi Ranjan (M.Tech-NIT)

In Oracle GROUP BY clause is used with SELECT statement to collect data from multiple records and group the results by one or more columns.

Syntax:

  1. SELECT expression1, expression2, ... expression_n,   
  2. aggregate_function (aggregate_expression)  
  3. FROM tables  
  4. WHERE conditions  
  5. GROUP BY expression1, expression2, ... expression_n;   

Parameters:

expression1, expression2, ... expression_n: It specifies the expressions that are not encapsulated within aggregate function. These expressions must be included in GROUP BY clause.

aggregate_function: It specifies the aggregate functions i.e. SUM, COUNT, MIN, MAX or AVG functions.

aggregate_expression: It specifies the column or expression on that the aggregate function is based on.

tables: It specifies the table from where you want to retrieve records.

conditions: It specifies the conditions that must be fulfilled for the record to be selected.

Oracle GROUP BY Example: (with SUM function)

Let's take a table "salesdepartment"

Salesdepartment table:

  1. CREATE TABLE  "SALESDEPARTMENT"   
  2.    (    "ITEM" VARCHAR2(4000),   
  3.     "SALE" NUMBER,   
  4.     "BILLING_ADDRESS" VARCHAR2(4000)  
  5.    )  
  6. /  

oracle group by example 1

Execute this query:

  1. SELECT item, SUM(sale) AS "Total sales"  
  2. FROM salesdepartment  
  3. GROUP BY item;  

Output

oracle group by example 2

The above example will show the total sales of every individual item.

Oracle GROUP BY Example: (with COUNT function)

Let's take a table "customers"

Here we are creating a table named customers. This table doesn't have any primary key.

Customer table:

  1. CREATE TABLE  "CUSTOMERS"   
  2.    (    "NAME" VARCHAR2(4000),   
  3.     "AGE" NUMBER,   
  4.     "SALARY" NUMBER,   
  5.     "STATE" VARCHAR2(4000)  
  6.    )  
  7. /  

oracle group by example 3

Execute this query:

  1. SELECT state, COUNT(*) AS "Number of customers"  
  2. FROM customers  
  3. WHERE salary > 10000  
  4. GROUP BY state;  

Output:

oracle group by example 4

Oracle GROUP BY Example: (with MIN function)

Let?s take a table "employees"

Employees table:

  1. CREATE TABLE  "EMPLOYEES"   
  2.    (    "EMP_ID" NUMBER,   
  3.     "NAME" VARCHAR2(4000),   
  4.     "AGE" NUMBER,   
  5.     "DEPARTMENT" VARCHAR2(4000),   
  6.     "SALARY" NUMBER  
  7.    )  
  8. /  

oracle group by example 5

Execute this query:

  1. SELECT department,   
  2. MIN(salary) AS "Lowest salary"  
  3. FROM employees  
  4. GROUP BY department;  

Output:

oracle group by example 6

Oracle GROUP BY Example: (with MAX function)

In this example, we are using "employees" table that is given above.

Execute this query:

  1. SELECT department,  
  2. MAX(salary) AS "Highest salary"  
  3. FROM employees  
  4. GROUP BY department;  

Output:

oracle group by example 7