SQL Group by, Having and Order by clause- Tutorial using Oracle
Group function - Sum
SQL> select sum(salary) from employee;
Output
SUM(SALARY)
-----------
157000
Group function - max
SQL> select max(salary) from employee;
Output
MAX(SALARY)
-----------
70000
Group function - min
SQL> select min(salary) from employee;
Output
MIN(SALARY)
-----------
2000
Group function - count
SQL> select count(*) from employee;
Output
COUNT(*)
----------
6
Using Group by on designation to find the sum of salary for each designation
SQL> select designation,sum(salary) from employee group by designation;
Output
DESIGNATION SUM(SALARY)
-------------------- -----------
Assistant 7000
professor 120000
Associate 30000
Giving an alias for the group function result
SQL> select designation,sum(salary) as total_salary from employee group by designation;
Output
DESIGNATION TOTAL_SALARY
-------------------- ------------
Assistant 7000
professor 120000
Associate 30000
Having clause to filter the group results
SQL> select designation,sum(salary) as total_salary from employee group by designation having sum(salary)>50000;
Output
DESIGNATION TOTAL_SALARY
-------------------- ------------
professor 120000
Ordering employees by their id field in the ascending order
SQL> select * from employee order by id;
Output
ID NAME DESIGNATION SALARY DEP_NO
---------- -------------------- -------------------- ---------- ----------
111 satish sharma Associate 10000 421
112 ram Associate 20000 421
113 tom Assistant 5000 422
114 jeff Assistant 2000 422
115 mohan professor 50000 422
116 venkat professor 70000 422
6 rows selected.
Ordering employees by their id field in the descending order
SQL> select * from employee order by id desc;
Output
ID NAME DESIGNATION SALARY DEP_NO
---------- -------------------- -------------------- ---------- ----------
116 venkat professor 70000 422
115 mohan professor 50000 422
114 jeff Assistant 2000 422
113 tom Assistant 5000 422
112 ram Associate 20000 421
111 satish sharma Associate 10000 421
6 rows selected.
Grouping by dep_no and designation to find the sum of salary.
SQL> select dep_no,designation,sum(salary) from employee group by dep_no,designation;
Output
DEP_NO DESIGNATION SUM(SALARY)
---------- -------------------- -----------
421 Associate 30000
422 Assistant 7000
422 professor 120000