Group by clause is used to divide the rows in table to different groups.
So, that we can apply group functions on each group.
Ex1:
-------
SQL> select deptno, sum(sal)
from emp
group by deptno;
As the standard emp tables has 3 types of deptno ( 10, 20, 30), 14 rows of the table are divided 3 groups. SUM( ) function is applied on each group.
Ex2:
---------
SQL> select job, avg(sal)
from emp
group by job;
Ex3:
------
SQL> select deptno, SUM(sal), MAX(sal), MIN(sal), AVG(sal), COUNT(*)
from emp
group by deptno;
Ex4:
------
SQL> select deptno, job, SUM(sal)
from emp
group by deptno, job;
In the above query, first grouping is done based on deptno and sub grouping is done based on job.
SQL> select deptno, SUM(sal), ename
from emp
group by deptno;
The above query will result in error.
Error: Not a group by expression.
The rule of GROUP BY clause:
-------------------------------
All the columns in the select list should use GROUP functions or should be included in the GROUP BY clause.
In the above query, ename is not satisfying the rule, we get the error.
HAVING Clause:
--------------
HAVING clause is used to filter the results of GROUP BY clause.
Ex:
------
SQL> Select deptno, SUM(sal)
from emp
GROUP BY deptno
HAVING SUM(sal) > 9000;
Query execution process:
---------------------------
--> First the rows are grouped
--> Second the group function is applied on the identified groups
--> Third the groups that match the criteria in the HAVING clause are displayed.
Existance of GROUP BY clause does not gaurantee the existance of HAVING clause, But
the existance of HAVING clause demands the existance of GROUP BY caluse.
Few more examples:
--------------------
SQL> Select deptno, AVG(sal), SUM(sal)
from emp
group by deptno
having AVG(sal) > 2500;
SQL> Select deptno, MIN(sal), MAX(sal)
from emp
where job ='CLERK'
GROUP BY deptno
HAVING MIN(sal) < 1000;
ORDER BY clause:
-------------------
ORDER BY clause is used to arrange the rows in ascending or in the descending order.
ORDER BY clause should be the last clause of the query.
An expression or column alias can be specified in the ORDER BY clause.
The default ordering of the data is ascending.
Numbers --> 0 - 9
Dates ---> Earliest - Latest
Strings --> A - Z
NULLS --> Last
Ex:
-----------
SQL> select * from emp
ORDER BY sal;
SQL> select * from emp
ORDER BY sal desc;
SQL> Select ename , job, deptno, hiredate
from emp
ORDER BY hiredate;
SQL> Select * from emp
ORDER BY job, ename;
SQL> Select ename , job, sal
from emp
where sal > 2500
ORDER BY JOB, ename DESC;
SQL> Select empno, ename, sal, sal*12 annual_sal
from emp
ORDER BY annual_sal;