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. 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 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;
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;
No comments:
Post a Comment