Gropu by / Having / Order by

Group By Clause

The group by clause is used with “select” to combine a group of rows based on the values of a particular column or expression. Aggregate functions are used to return summary information for each group. The aggregate functions are applied to the individual groups.

Group By Functions or Aggregate Functions

The aggregate functions produce a single value for entire table or a group. They return results based on groups of rows. By default all the rows in a table are treated as one group.

There are Five Types of aggregate functions

1. Count

The count function is used to find the number of records or non – null columns values in the given column or expression

Syntax : count(* | distinct <column name>)

Examples :

write a query to count total number of records in the given table

Ø select count(*) from emp;

COUNT(*)

---------

14

2. write a query to count, how many types of jobs available in the emp table

Ø select count(job) from emp;

COUNT(JOB)

----------

14

(or)

Ø select count(distinct job) from emp;

COUNT(DISTINCTJOB)

------------------

5

2. Sum :

This command is used to find the sum of all Values of given numerical columns.

Syntax : Sum(distinct <numerical column>)

Examples :

Write a query to find sum of sal for all employees in the emp table

Ø select sum(sal) from emp;

SUM(SAL)

---------

34625

3. Max :

This command is used to find the maximum value from the given numerical column.

Syntax : Max(numerical column)

Example :

Write a query to find maximum sal earning by an employee in the emp table

Ø select max(sal) from emp;

MAX(SAL)

---------

5000

4. Min :

This command is used to find the minimum value from the given numerical column.

Syntax : Min(numerical column)

Example:

Write a query to find minimum sal earning by an employee in the emp table

Ø select min(sal) from emp;

MIN(SAL)

---------

800

5. Avg :

This command is used to find the average value from the given numerical column.

Syntax : Avg(distinct <numerical column>)

Example :

Write a query to find average salary of all employee in the emp table

Ø select avg(sal) from emp;

AVG(SAL)

---------

2308.3333

Examples using Group By Clause

1. List The Department Numbers And Number Of Employees In Each Department

Ø select deptno, count(*) from emp group by deptno ;

1. List the jobs and number of employees in each job

Ø select job, count(*) from emp group by job;

1. List The Total Salary, Maximum And Minimum Salary And The Average Salary Of Employees Job Wise

Ø select job, sum(sal), avg(sal), max(sal), min(sal) from emp group by job ;

Having Clause

The having clause is used to specify which groups are to be displayed that means it restricts the groups which returns on the basis of aggregate functions

(Or)

This is used to define condition on the columns used after the group by clause. It is used to restrict the number of rows by specifying a condition with the grouped columns

Examples :

1. List The average salary of all the Departments employing more than 5 people

Ø select deptno, avg(sal) from emp group by deptno having count(*)>5;

1. List the jobs of all the employees whose maximum salary is >=5000

Ø select job, max(sal) from emp group by job having max(sal)>=5000;

Order By Clause

The order by clause is used to arrange the rows in Ascending or in descending order. By default the select statement displays in ascending order. If you want to display in descending order, specify the “desc” keyword after the column name.

Multiple columns are ordered one within another, and the user can specify whether to order them in ascending or in descending order.

1. List The Empno,Ename,Sal In Ascending Order By Salary

Ø select empno,ename,sal from emp order by sal;

1. List The Employee Name In Ascending Order And Their Salaries In Descending Order

Ø select ename ,sal from emp order by ename, sal desc;