Group Functions

Group functions are built-in SQL functions that operate on groups of rows and return one value for the entire group.

 

We have 5 GROUP functions,

1)   Sum

2)   Max

3)   Min

4)   Avg

5)   Count

 

Sum – returns total value

Max – returns maximum value

Min – returns minimum value

Avg – returns average value

Count – returns number of records

 

Ex – 1) display the maximum salary, minimum salary and total salary from employee

 Select Max(Sal), min(Sal), Sum(sal) From Emp;

 To give aliases for the columns :-

 Select Max(sal) "High Sal",

           Min(sal) "Low Sal",

           Sum(Sal) "Total Sal" 

From Emp;

 

3) The below query gives the total number of employees

 Select Count(*) , Count(Empno) from Emp;

4) The below query gives the number of employees who have a commission

 Select Count(*), Count(Comm) from Emp; 

 5) List the number of employees in department 30

 Select Count(*) from Emp where Deptno = 30;

 

ASSIGNMENT

1) Display the total salary in department 30

 Select Sum(Sal) from Emp Where Deptno = 30; 

2) List the number of clerks in department 20

     Select Count(*) From Emp

     Where Deptno = 20

         And Job = 'CLERKS';

3) List the highest and lowest salary earned by salesmen

 Select Max(Sal), Min(Sal) 

  From Emp

Where Job = 'SALESMAN';

GROUPING

It segregates the results based on one or more columns and computing an aggregate. An Aggregate function is also called a Group Function.

Grouping is done by using the ‘group by’ clause.

For Ex – 1) Display the total salary of all departments

 Select Deptno, Sum(Sal) From Emp

 Group By Deptno; 

2) Display the maximum of each job

 Select Max(Sal), Job

 From Emp

 Group By Job; 

 

HAVING

‘Having’ is used to filter the grouped data.

‘Where’ is used to filter the non-grouped data.

 

‘Having’ should be used after group by clause

‘Where’ should be used before group by clause

 

For Ex.1) Display job-wise the highest salary only if the highest salary is more than Rs1500

 Select Job, Max(Sal)

  From Emp

  Group By Job

  Having Max(Sal) > 1500; 

2) Display job-wise highest salary only if the highest salary is more than 1500 excluding department 30. Sort the data based on highest salary in the ascending order.

  Select Job, Max(Sal)

  From Emp

  Where Deptno <> 30

  Group By Job

  Having Max(Sal) > 1500

  Order by 2 Asc;  

RESTRICTIONS ON GROUPING

- we can select only the columns that are part of ‘group by’ statement

If we try selecting other columns, we will get an error as shown below,

 Select Deptno, Job, Sum(Sal), Sum(Comm)

 From Emp 

  Group By Deptno;

Error: Error at line 1

ORA-00979: Not a GROUP BY expression.

The above query is an error because ‘job’ is there in the select query but not in the group by query.

If it is enclosed in any of the group functions like sum(sal) etc. – then it is not an error. But whatever table Columns is included in the select query must also be included in the group by query.

 

The above problem can be overcome with the following query as shown below,

  Select Deptno, Job, Sum(Sal), Sum(Comm)

 From Emp 

  Group By Deptno, Job; 

The below query is also correct to rectify the above error,

  Select Deptno, Job, Sum(Sal), Sum(Comm)

 From Emp 

  Group By Deptno, Job

  Order by Deptno; 

Whatever is there in the select statement must be there in the group by statement. But, whatever is there in the group by statement need not be present in the select statement. This is shown in the above two corrected queries.

 

ASSIGNMENT

1) Display the department numbers along with the number of employees in it

2) Display the department numbers which are having more than 4 employees in them

3) Display the maximum salary for each of the job excluding all the employees whose name ends with ‘S’

4) Display the department numbers which are having more than 9000 as their departmental total salary

5) Display job-wise maximum salary.

6) Display the departments that are having more than 3 employees under it.

7) Display job-wise average salaries for the employees whose employee number is not from 7788 to 7790.

8) Display department-wise total salaries for all the Managers and Analysts, only if the average salaries for the same is greater than or equal to 3000.

9) Consider the following table: -

          Assignment 5, 6, 7 & 8 are based on the following table

Table Name: SKILLS

ID	Name
101	Oracle
102	Oracle
103	Oracle
101	Oracle
102	Java
103	Java
101	Java
102	Java
103	Java
101	Java
101	Java
101	Oracle
101	VB
102	ASP

 

10) Select only the duplicate records along-with their count.

11) Select only the non-duplicate records.

12) Select only the duplicate records that are duplicated only once.

13) Select only the duplicate records that are not having the id=101.

 

 

Rollup & Cube Operator:

Rollup and Cube are special operators.

Rollup:

======

Deptno + Job Wise Total Salary

+

Deptno Wise Total Salary

+

Overall Salary (Grand Total).

 

Cube:

=====

Deptno + Job Wise Total Salary

+

Deptno Wise Total Salary

+

Job Wise Total Salary

+

Overall Salary (Grand Total).