Monday, August 6, 2012

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;


No comments:

Post a Comment

Thank you :
- kareem