Summary queries

Summary Queries Aggregate Functions SUM() MAX() MIN() AVG() COUNT() Group By clause will eliminate supplicates for a value and sort the values in ascending manner. Group By clause can also do aggregation within that value. Group functions on columns ignore Null values Select deptno From emp Group by deptno; Group by clause -- To see maximum salary in each department select deptno,max(sal) from emp group by deptno; Having clause Having clause is used to filter summary records Filtering of grouped results is done in having clause -- To see deptno who have maximum salary more than 3000 select deptno,max(sal) from emp group by deptno Having max(sal) > 3000 Nested Grouping -- To see Job wise maximum salary within each department select Job,deptno,max(sal) from emp group by JOB, deptno; -- To see Department-wise maximum salary within each job select Deptno,job,max(sal) from emp group by DEPTNO,job; Nesting of Aggregate Functions: When an aggregate function is passed as a parameter to an another aggregate function then Group by is a mandatory clause For e.g. – To display the highest average salary within jobs. Select max( avg(sal)) From emp Group by Job True cond abt aggr func 1)you can pass column names, exp, const, func as parameters to Agg fun’s 2)you can mix single row columns on aggr func in column list of select st by grouping on single row cols.Nesting of Aggregate Functions: When an aggregate function is passed as a parameter to an another aggregate function then Group by is a mandatory clause For e.g. – To display the highest average salary within jobs. Select max( avg(sal)) From emp Group by Job