Group functions let you compute a single summary value from a column of individual values. The basic group functions are AVG, COUNT, MIN, MAX and SUM. Their use is straightforward. Group functions ignore NULL values.

Suppose you want to compute the average salary of all employees listed in the EMPLOYEE table. The SQL query would look like this:

The results table would look like this:

AVG(SALARY)

-----------

2812.5

Suppose you want to find the minimum and maximum salaries listed in the employee table. The SQL query would look like this:

The results table would look like this:

Consider that the values in the Salary column represent monthly salaries. Suppose you want to find the total amount paid per month in salary to employees in Department 40. The SQL query would look like this:

The results table would look like this:

SUM(SALARY)

---------

10550

Suppose you want to find out how many employees are listed in the EMPLOYEE table. The SQL query would look like this:

The results table looks like this:

COUNT(*)

--------

12

DISTINCT may be used with the COUNT function to count only distinct rows.

Suppose you want to know how many different jobs are listed in the EMPLOYEE table. The following query will return this information.

The results table is shown here:

COUNT(DISTINCTJOB)

------------------

6 

NOTE: Group functions may not be listed with column names in the SELECT clause (unless you also use the GROUP BY command as described below). For example, suppose you want to find the name of the employee who is paid the lowest salary. The query, "SELECT NAME, MIN(SALARY) FROM EMPLOYEE", would generate an error. A subquery will return the desired information. Go to SQL Exercise 9 to learn about how to use subqueries.

The GROUP BY clause causes a select statement to produce one summary row for all selected rows that have the identical value as specified in the GROUP BY clause. The column referred to in the GROUP BY clause does not have to be in the select clause. However, if the column referred to in the GROUP BY clause is not listed in the select you will not know with which group the returned data is associated. For example, suppose you want to know the total amount paid in salary to each department. The query would look like this:

The results table from this query would look like this:

SUM(SALARY)

-----------

4600

8000

10550

5450

5150

Without the DEPTNO column in the select clause, you have data, but don't know which department is associated with each row of data. To solve this problem, write the query like this:

This results table shows information that is useful.

Suppose you want to know the number of employees within each department from the EMPLOYEE table. The query would look like this:

The results table would look like this:

Suppose you want to find the minimum salary, maximum salary, and the average salary for each department. The SQL implementation for this query would be:

MAX(SALARY), AVG(SALARY) ç group functions in select clause

GROUP BY DEPTNO; ç GROUP BY clause

The results table would look like this:

The HAVING clause is similar to the WHERE clause. The WHERE clause acts on individual rows while the HAVING clause acts on groups. The HAVING clause is used to determine which groups the GROUP BY is to include. For instance, suppose you want to find which departments have at least 3 employees listed in the EMPLOYEE table. Using the query above with the HAVING clause we choose to return only the rows with more than 2 employees per department. That query would be as follows:

HAVING COUNT(*) > 2; ç HAVING clause

The results table looks like this:

Suppose you want to find out how much total salary is paid out per month to all employees listed in the EMPLOYEE table. Suppose you are feeling very generous and want to find out how much total monthly salary you would pay if you give every employee a 3% raise. A single SQL query would return this information.

The results table looks like this:

You may choose names for columns in your results table by including the names in the SELECT clause as illustrated below:

This query returns the following results table:

For you to try:

1. Suppose you want to know the minimum commission paid, the maximum commission paid, the sum of all commissions paid, and the number of employees paid commission. Write one SQL query to find this information.
2. Suppose you want to find out how many employees are listed in each job category. Write a SQL query to find this information. 
3. Suppose you want to find out how much total salary was paid to each different job category. That is, how much total were clerks paid, how much were managers paid, etc. Write a SQL query to find this information.

Go here to check your answers.

Last revised 10/21/1997 by J. Warren, Kennesaw State University

Department of Computer Science and Information Systems