CodesJava

Easy learning with example program codes

HAVING clause in Oracle

ORACLE HAVING
Oracle HAVING clause is used with the Oracle GROUP BY Clause, however, it does not have a mandatory existence but still is important enough, as it is used to return the groups of rows only when the condition is TRUE.

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

SELECT expressions
FROM table_name
GROUP BY columns
HAVING having_conditions;

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.
having_conditions: It is used to specify the conditions that are applicable only to the result obtained after the successful execution of the GROUP BY command, to restrict the groups of returned rows.

Example:
Students Table:

ID	NAME	AGE
1	Joy	10
2	Smiley	13
3	Happy	11
4	James	13
5	Bond	10

Query:

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

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 with a restriction of having age greater than 10.

Syntax: Oracle GROUP BY with WHERE clause.

SELECT expressions
FROM table_name
WHERE conditions
GROUP BY columns
HAVING having_conditions;

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

Example:
Students Table:

ID	NAME	AGE
1	Joy	10
2	Smiley	12
3	Happy	11
4	James	13
5	Bond	10

Query:

SELECT age
FROM students
WHERE  id > 2  
GROUP BY age
HAVING age > 10;

Output:

AGE
11
13

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 id must be greater than 2 and with a restriction of having age greater than 10.

Syntax: Oracle GROUP BY with ROLLUP.

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

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:

ID	NAME	AGE
1	Joy	10
2	Smiley	13
3	Happy	11
4	James	13
5	Bond	10

Query:

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

Output:

NAME	AGE
Joy	13
Joy	11
Smiley	13
Smiley	11
Happy	13
Happy	11
James	13
James	11
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, and with the restriction of having age greater than 10.

Syntax: Oracle GROUP BY with an aggregate function.

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

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:

ID	NAME	AGE
1	Joy	10
2	Smiley	13
3	Happy	11
4	James	13
5	Bond	10
6	Jerry	13
7	Tom	12
8	Brutus	12

Query:

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

Output:

AGE	Number of Students
10	2
13	3
12	2

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, and with the restriction of having count greater than 1.

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
6	Jerry	100	13
7	Tom	99	12
8	Brutus	60	12

Query:

SELECT age, SUM(marks) AS “Total Marks”
FROM students
GROUP BY age
HAVING SUM(marks) > 100
ORDER BY age;

Output:

AGE	Total Marks
10	160
12	159
13	285

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, and with the restriction of having sum of marks greater than 100.

Please follow and like us:
error









Copyright © 2019 CodesJava DMCA.com Protection Status