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

**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__

**List The Department Numbers And Number Of Employees In Each Department**

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

**List the jobs and number of employees in each job**

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

**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 :**

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

**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.

**List The Empno,Ename,Sal In Ascending Order By Salary**

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

**List The Employee Name In Ascending Order And Their Salaries In Descending Order**

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

Is there any way to export all views on a schema along with their data types and constraints on them using select query or using different approach

ReplyDelete