Group By

Oracle GROUP BY Clause

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.


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



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:

  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;  



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:

  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;  



oracle group by example 4

Oracle GROUP BY Example: (with MIN function)

Let?s take a table "employees"

Employees table:

  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;  



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;  



oracle group by example 7