Group by /Order by / Having Clause

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 )

Examples:

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

SQL> select count (*) from emp;

COUNT (*)

---------

14

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

SQL> select count(job) from emp;

COUNT(JOB)

----------

14

(or)

SQL> 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 )

Examples:

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

SQL> 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

SQL> 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

SQL> 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 )

Example :

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

SQL> 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

SQL> SELECT DEPTNO, COUNT (*) FROM EMP GROUP BY DEPTNO;

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

SQL> 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

SQL> 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

SQL> 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

SQL> 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;