GROUP BY clause in Oracle

ORACLE GROUP BY
Oracle GROUP BY clause is used with the Oracle SELECT statement, however, it does not have a mandatory existence but still is important enough, as it is used to collect data from multiple records and then to group the results by one or more columns.

Syntax: To group the rows by values in multiple columns.

SELECT expressions
FROM table_name
GROUP BY columns;

Parameters:
expressions: It is used to specify the columns or calculations to be retrieved.
table_name: It is used to specify the name of the table from which you want to retrieve the records.
columns: It is used to specify the list of columns to be grouped.

Example:
Students Table:

STUDENT_ID STUDENT_NAME STUDENT_AGE
1 Joy 10
2 Smiley 13
3 Happy 11
4 Tom 13
5 Jerry 10

Query:

SELECT age
FROM students
GROUP BY age;

Output:

AGE
10
13
11

Explanation:
The ‘students’ is an already existing table. Here we are using the Group By clause to find unique student ages from the ‘students’ table.

Syntax: Oracle GROUP BY with WHERE clause.

SELECT expressions
FROM table_name
WHERE conditions
GROUP BY columns;

Parameters:
conditions: It is used to specify the conditions to be strictly followed for selection.
Example:

Students Table:

STUDENT_ID STUDENT_NAME STUDENT_AGE
1 Joy 10
2 Smiley 13
3 Happy 11
4 Tom 13
5 Jerry 10

Query:

SELECT age
FROM students
WHERE  age > 10  
GROUP BY age;

Output:

AGE
13
11

Explanation:
The ‘students’ is an already existing table. Here we are using the Group By clause to find unique student ages from the ‘students’ table, but with a condition that ages must be greater than 10.

Syntax: Oracle GROUP BY with ROLLUP.

SELECT expressions
FROM table_name
GROUP BY ROLLUP (column_1, column_2, .., column_n);

Parameters:
ROLLUP: It is used to specify multiple levels of grouping. These multiple levels of grouping are computed at once.
columns: It is used to specify the list of columns to be grouped.

Example:
Students Table:

STUDENT_ID STUDENT_NAME STUDENT_AGE
1 Joy 10
2 Smiley 13
3 Happy 11
4 Tom 13
5 Jerry 10

Query:

SELECT name, age
FROM students
GROUP BY ROLLUP (name, age);

Output:

ID SHOP
Joy 10
Joy 13
Joy 11
Smiley 10
Smiley 13
Smiley 11
Happy 10
Happy 13
Happy 11
James 10
James 13
James 11
Bond 10
Bond 13
Bond 11

Explanation:
The ‘students’ is an already existing table. Here we are using the Group By clause to find unique student names and ages from the ‘students’ table, but with multiple levels of grouping.

Syntax: Oracle GROUP BY with an aggregate function.

SELECT expressions, aggregate_function (aggregate_expression)  
FROM table_name  
WHERE conditions  
GROUP BY columns;

Parameters:
Aggregate_function: It is used to specify the aggregate functions. Some of the aggregate functions are SUM, COUNT, MIN, MAX and AVG.
Aggregate_expression: It is used to specify the column or expression to be utilised by the aggregate function.

Example 1: Oracle GROUP BY with COUNT function
Students Table:

 

STUDENT_ID STUDENT_NAME STUDENT_AGE
1 Joy 10
2 Smiley 13
3 Happy 11
4 Tom 13
5 Jerry 10

Query:

SELECT age, COUNT(*) AS “Number of Students”
FROM students
GROUP BY age;

Output:

AGE Number of Students
10 2
13 2
11 1

Explanation:
The ‘students’ is an already existing table. Here we are using the Group By clause with an aggregate function COUNT to count the number of students of the same age group.

Example 2: Oracle GROUP BY with SUM function
Students Table:

ID NAME MARKS AGE
1 Joy 90 10
2 Smiley 100 13
3 Happy 80 11
4 James 85 13
5 Bond 70 10

Query:

SELECT age, SUM(Marks) AS “Total Marks”
FROM students
GROUP BY age;

Output:

AGE Total Marks
10 160
13 185
11 80

Explanation:
The ‘students’ is an already existing table. Here we are using the Group By clause with an aggregate function SUM in order to sum up the marks of the students of the same age group.

Please Share