Group by, Grouping Functions and Having Clause

Aggregate or Group Functions:
Ø  These functions return a single row based on groups of rows.
Ø  These functions can appear in select lists and having clauses only.
Ø  These functions operate on sets of rows to give one result per group.
Ø  The sets may be the whole table or the table split into groups.
Guidelines to use group functions:
Ø  DISTINCT makes the function to consider only non-duplicate values.
Ø  ALL makes the function to consider every value including duplicates.
Syntax: GroupFunctionName(distinct /all col)
Ø  The data types for arguments may be CHAR, VARCHAR2, NUMBER OR DATE.
Ø  All group functions except count (*) ignore NULL values. To substitute a value for NULL value, Use the NVL Function.
Ø  When a group function is declared in a select list, no single row columns should be declared.
Ø  When a group function is declared in a select list, other columns can be declared, but they should be grouped columns, and all the non-functional columns should be declared into group by clause.
Average Function:
Syntax: AVG (Distinct /All cols)
Ø  It Returns the AVERAGE value of column.
Ø  It ignores NULL values.
Ex:
·         Select avg(sal), avg(distinct sal) from emp;
·         Select avg(comm), avg(distinct comm) from emp;
Sum Function:
Syntax: Sum(distinct/all col)
Ø  It returns the sum value of column.
Ø  It ignores NULL values.
Ex:
·         Select sum(sal), sum(distinct sal) from emp;
·         Select sum(comm), sum(distinct comm) from emp;
Maximum Function:
Syntax: Max(Distinct /All col)
Ø  It returns the maximum value of column.
Ø  It ignores NULL values.
Ex:
·         Select max(sal), max(distinct sal) from emp;
·         Select Max(comm), max(distinct comm) from emp;
Minimum Function:
Syntax: Min(distinct / all col)
Ø  It returns the minimum value of the column.
Ø  It ignores NULL values.
Ex:
·         Select min(sal), min(distinct sal) from emp;
·         Select min(Comm), min(distinct Comm) from emp;
Standard Deviation Function:
Syntax: STDDEV(distinct /All col)
Ø  It returns the standard deviation of the column.
Ø  It ignores NULL values.
Ex:
·         Select stddev(sal), stddev(distinct sal) from emp;
·         Select stddev(Comm), stddev(distinct Comm) from emp;
Variance Function:
Syntax: variance(distinct / All col)
Ø  It returns the variance of N.
Ø  It ignores the NULL values.
Ex:
·         Select variance(sal), variance(distinct sal) from emp;
·         Select variance(Comm), variance(distinct variance) from emp;
Count Function:
Syntax: Count(*/Distinct/All col)
Ø  It returns the number of rows in the query.
Ø  It ‘*’ is used returns all rows, including duplicated and NULL’s.
Ø  It can be used to specify the count of all rows or only distinct values of col.
Ex:
·         Select count(*) from emp;
·         Select count(job), count(distinct job) from emp;
·         Select count(sal), count(Comm) from emp;
·         Select count(empno), count(distinct MGR) from emp;
Creating Groups of Data:
Ø  The group by clauses is used to decide the rows in a table into groups. 

Syntax1:
·         Select columnName1, columnName2,… From TableName where condition(s) group by columnNmae(s) Order by ColumnName(s);
Syntax2:
·         Select columnName1, GRP_FUN(Column) from TableName where Condition(s) Group by ColumnName(s) Order by Column(s);
Guidelines to use Group by clause:
Ø  If the group function is included in a select clause, we should not use individual result columns.
Ø  The extra non group functional columns should be declared in the group by clause.
Ø  Using where clause, rows can be pre excluded before dividing them into groups.
Ø  Column aliases cannot be used in group by clause.
Ø  By default, rows are sorted by ascending order of the columns included in the group by list.
Ø  The column applied upon group by clause need not be part of select list.
Ex:
·         Select deptno from emp group by deptno;
·         Select job from emp group by job;
·         Select mgr from emp group by mgr;
·         Select to_char(hiredate, ’YYYY’) yeargroup from emp group by to_char(hiredate, ’YYYY’);
·         Select to_char(hiredate, ’Month’) Monthgroup from emp group by to_char(hiredate, ’Month’);
·         Select to_char(hiredate, ’Month’) Monthgroup from emp where to_char(hiredate, ’Month’) <> ’September’ Group by to_char(hiredate, ’Month’);
Creating Group wise summaries:
Ex:
·         Select deptno, avg(sal) from emp group by deptno;
·         Select deptno, avg(sal) from group by deptno order by avg(sal);
·         Select deptno, min(sal), max(sal) from emp group by deptno, job;
Note:
Ø  The above specification falls under the principle of groups within groups.
Ex:
·         Select deptno, min(sal), max(sal) from emp where job= ’CLERK’ Group by Deptno;
·         Select deptno, sum(sal), avg(sal) from emp where job = ’CLERK’ Group by deptno;
Excluding Groups of Results:
Having Claus:
Ø  It is used to specify which groups are to be displayed.
Ø  The clause is used to filter data that is associated with group functions.
Syntax:
·         Select Column, Group_Function From Table [where condition(s)] [ Group by Group_by_expr] [Having Group_condition(s)] [Order by column_name / Alias ];
Steps Performs By Having Clause:
Ø  First the rows are grouped.
Ø  Second the group function is applied to the identified groups.
Ø  Third the groups that match the criteria in the having clause are displayed.
Ø  The having clause can precede group by clause, but it is more logical to declare it after group by clause.
Ø  Group by clause can be used, without a group functions in the select list.
Ø  If rows are restricted based on the result of a group function, we must have a group by clause as well as the having clause.
Ø  Existence of group by clause does not guarantee the existence of having clause, but the existence of having clause demands the existence of group by clause. 
Ex:
·         Select deptno, avg(sal) from emp group by deptno, having max(sal) > 2900;
·         Select job, sum(sal) payroll from emp where job not like ’SALES%’ group by job having sum(sal) > 5000 order by sum(sal);
·         Select Deptno, min(sal), max(sal) from emp where job=’CLERK’  group by deptno having min(sal) < 1000;
·         Select depnto, sum(sal) from emp group by deptno having count(deptno) > 3;
·         Select deptno, avg(sal), sum(sal), max(sal),min(sal) from emp group by deptno Having avg(sal) > 2500;
Nesting of Group Functions:
Ø  Group Functions can be nested to a depth of two levels.
Ex:
·         Select max(avg(sal)) from emp group by deptno;
·         Select max(sum(sal)), min(sum(sal)) from emp group by deptno;

·         Select max(sum(sal)), min(avg(sal)) from emp group by job;