Oracle:Group Functions

Main Page >> Oracle and SQL >> Workbook >> Functions >> Group Functions

So far the queries return either all the rows from the tables concerned, or a subset, depending on what search criteria has been specified. In some cases you may wish to carry out analytical work, which involves counting, producing averages, or other statistical operations. This involves using group functions that aggregates the data.

Group Functions

The following are the main group functions.

AVG(X)

COUNT(X)

MAX(X)

MIN(X)

SUM(X)

STDDEV(X)

VARIANCE(X)

where X can be any attribute name, e.g., AVG(sal).

In general group functions ignore null values.

Example Queries Involving Group Functions

To find the average salary of clerks:

SELECT  AVG(SAL)  
FROM  EMP  WHERE  JOB  =  'CLERK';

To find the total salary and total commission of salespeople:

SELECT SUM(SAL), SUM(COMM) 
FROM EMP 
WHERE JOB = 'SALESMAN';

To find the average annual pay of all salespeople:

SELECT 12*AVG(SAL + COMM) 
FROM EMP 
WHERE JOB = 'SALESMAN';

To find the highest and lowest salaries and the difference between them:

SELECT MAX(SAL), MIN(SAL), MAX(SAL) - MIN(SAL) AS DIFFERENCE
FROM EMP;

To count the number of employees:

SELECT COUNT (*) 
FROM EMP;

To count the number of different jobs held by employees in department 30:

SELECT COUNT (DISTINCT JOB) AS JOB_COUNT
FROM EMP 
WHERE DEPTNO = 30;

Summarising Several Groups - GROUP BY

To summarise several groups within the same query, use the GROUP BY clause. This clause divides a table into groups of rows so that the rows in each group have the same value in a specified column. You can select groups to display by using the HAVING clause with the GROUP BY clause.

The format of the GROUP BY clause is:

GROUP BY column name

HAVING condition expression involving some property of the group (i.e., a group function) and a constant

Example Queries Using GROUP BY

To list the average salary for each department:

SELECT  DEPTNO,  AVG(SAL) AS AVG
FROM  EMP  
GROUP  BY  DEPTNO;

To list the average salary for each department, whose average salary is greater than 2500:

SELECT  DEPTNO,  AVG(SAL) AS AVG
FROM  EMP 
GROUP  BY  DEPTNO
HAVING  AVG(SAL)  >  2500;

Note, you can still apply restrictions on the non-aggregate data using the normal WHERE clause:

SELECT  DEPTNO,  SUM(SAL) AS SAL_Total
FROM EMP
WHERE JOB = 'MANAGER'
GROUP BY DEPTNO
HAVING  SUM(SAL)  >  2500;

To find the average annual salary of the non-managerial staff in each department:

SELECT DEPTNO, 12*AVG(SAL)  FROM EMP 
WHERE JOB NOT IN ('MANAGER', PRESIDENT') 
GROUP BY DEPTNO;

To count the employees and calculate the average annual salary for each job group in each department:

SELECT  DEPTNO,  JOB,  COUNT(*) AS count,  12*AVG(SAL) AS avg_sal
FROM  EMP 
GROUP  BY  DEPTNO,  JOB;

(COUNT(*) counts the number of rows in the table satisfying the query).

To list the average annual salary for all job groups with more than two employees:

SELECT  JOB,  COUNT(*),  12  *  AVG(SAL) AS total_sal
FROM  EMP  
GROUP  BY  JOB 
HAVING  COUNT(*)  >  2;

To list the total annual salary and commission for all job groups in each department:

SELECT DEPTNO, JOB, SUM(SAL+NVL(COMM,0)) AS SUMMARY
FROM EMP
GROUP BY DEPTNO, JOB;

NVL is a function that will convert nulls to a specific value (try the query without the function to see the difference made).

You can nest aggregate functions. For example, the following example calculates the average of the maximum salaries of all the departments:

SELECT AVG(MAX(sal)) AS avg_max_sal
FROM EMP
GROUP BY deptno;

Next Step

Subqueries.