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.

Syntax:

snippet
SELECT expression1, expression2, ... expression_n, 
aggregate_function (aggregate_expression)
FROM tables
WHERE conditions
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:

snippet
CREATE TABLE  "SALESDEPARTMENT" 
   (	"ITEM" VARCHAR2(4000), 
	"SALE" NUMBER, 
	"BILLING_ADDRESS" VARCHAR2(4000)
   )
/
oracle group by example 1

Execute this query:

snippet
SELECT item, SUM(sale) AS "Total sales"
FROM salesdepartment
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:

snippet
CREATE TABLE  "CUSTOMERS" 
   (	"NAME" VARCHAR2(4000), 
	"AGE" NUMBER, 
	"SALARY" NUMBER, 
	"STATE" VARCHAR2(4000)
   )
/
oracle group by example 3

Execute this query:

snippet
SELECT state, COUNT(*) AS "Number of customers"
FROM customers
WHERE salary > 10000
GROUP BY state;

Output:

oracle group by example 4

Oracle GROUP BY Example: (with MIN function)

Let?s take a table "employees"

Employees table:

snippet
CREATE TABLE  "EMPLOYEES" 
   (	"EMP_ID" NUMBER, 
	"NAME" VARCHAR2(4000), 
	"AGE" NUMBER, 
	"DEPARTMENT" VARCHAR2(4000), 
	"SALARY" NUMBER
   )
/
oracle group by example 5

Execute this query:

snippet
SELECT department, 
MIN(salary) AS "Lowest salary"
FROM employees
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:

snippet
SELECT department,
MAX(salary) AS "Highest salary"
FROM employees
GROUP BY department;

Output:

oracle group by example 7
Related Tutorial
Follow Us
https://www.facebook.com/Rookie-Nerd-638990322793530 https://twitter.com/RookieNerdTutor https://plus.google.com/b/117136517396468545840 #
Contents