Functions for Use with GROUP BY Clauses

Some functions are designed specifically for use with GROUP BY. These are sometimes called grouping functions or aggregate functions. You can also choose to run these functions over the entire result set of a query, treating all the rows as a single group. We saw this used in queries like this:

select count(*)
from employee;

This query will count the number of rows in the employee table.

We typically use these functions over groups, as shown here:

select job, count(job)
from employee
group by job;

This will tell us how many employees are in each job grouping.

A list of the more useful grouping functions is shown in Table 8.7.

Table 8.7. Grouping Functions Function
Purpose

avg(column)
Returns the average value in column.

count(column)
Returns the number of values in column.

min(column)
Returns the smallest value in column.

max(column)
Returns the largest value in column.

std(column)
Returns the standard deviation of the values in column.

sum(column)
Returns the sum of values in column.