Order by,Group by and Having Clause in oracle

To study Order by, Group by and having clause we use employee table with data as shown below:

Selecting employee table



1. ORDER BY:- This clause sort data of table in ascending or descending order respective to column value in table.
If we pass more than one column in order by clause then sorting will done according to column order, i.e. first sorting will be done as per first column then second column and so on.
We can specify “DESC” for descending order otherwise default is ascending order.

Syntax
Select * from tablename
ORDER BY column1 [DESC], [column2 [DESC], column3 [DESC], ……. column n [DESC] ];

In above syntax “ORDER BY” is clause, “DESC” is keyword for descending order.

Use of ORDER BY clause



In above query sorting based on employee name in ascending order are displayed.

Use of ORDER BY clause with DESC



In above query sorting based on employee name in descending order are displayed.

Use of ORDER BY clause with DESC on two columns



In above query sorting based on employee name in descending order then based on employee salary are displayed.

Use of ORDER BY clause with DESC on two columns



In above query sorting based on employee name in descending order then based on employee department are displayed.

Use of ORDER BY clause with DESC on two columns



In above query sorting based on employee salary in descending order then based on employee name are displayed.

2. GROUP BY:- This clause is used for grouping of records based on same data in column.
We should use aggregate function with group by clause to get desired output.

Syntax
Select column 1, …..,aggregate function(column n), from tablename
GROUP BY column1, [column2, column3 , ……. column n];

In above syntax “GROUP BY” is clause.

Use of GROUP BY clause



In above query all department name with their total salary are displayed.

Use of GROUP BY clause



In above query all department name with their average salary are displayed.

3. HAVING:- This clause is used with “GROUP BY” clause to filter grouped data.
To filter grouped data based on specified condition we cannot use “WHERE” clause with “GROUP BY” clause.

Syntax
Select column 1, …..,aggregate function(column n), from tablename
GROUP BY column1, [column2, column3 , ……. column n]
HAVING condition;

In above syntax “GROUP BY” and “HAVING” is clause.

Use of HAVING with GROUP BY clause



In above query department name with average salary greater than 18000 are displayed.

Use of HAVING with GROUP BY clause



In above query department name with average salary less than or equal to 18000 are displayed.

Use of HAVING with GROUP BY clause



In above query department name with total salary greater than 36000 are displayed.

Use of HAVING with GROUP BY clause



In above query department name with total salary less than or equal to 50000 are displayed.

I hope this article will help you