Group functions are applied on a set of rows and are not like the single row functions which are used to manipulate a single row. The set of rows may have all the rows from the table or all the rows from a group which is formed by splitting the table based on some condition. They will output a single result for a group and can be used to obtain summary information about the group. Group functions can also be used to divide the table into different groups of rows and to define searching criteria on those groups.

There are many types of group functions available in Oracle. Some of them are:

- SUM
- AVG
- MAX
- MIN
- COUNT
- STDDEV
- VARIANCE

The syntax to use Group Functions is like:

SELECT <column, GROUP_FUNCTION(column)> FROM <table name> WHERE <Condition>

Here:

- SELECT Keyword is used to specify the name of columns on which grouping is to be done.
- GROUP_FUNCTION is used to denote the grouping function used.
- FROM specifies the name of the table on which grouping is to be done.
- WHERE can be used to specify a condition on basis of which grouping will be done.

Since Group Functions are not the single row functions, we can describe them with the help of a block diagram.

In this many rows participate in the use of Group Functions and a single result for all the rows is obtained.

Suppose we have a table called Author in our database which looks like:

Author

AUTHOR_ID | FIRST_NAME | LAST_NAME | PHONE | BOOKS | SALARY | COMPANY | DOB | ADDRESS |

1 | John | Doe | 999123456 | 5 | 24000 | ABC | 16-Aug-63 | California |

5 | Steve | Brown | 698296556 | 5 | 45000 | ABC | 29-Nov-70 | Boston |

7 | Ben | Stuart | 877034486 | 4 | 15000 | XYZ | 11-Mar-68 | |

4 | April | Berker | 573902845 | 9 | 78900 | ABC | 30-Jun-50 | London |

2 | Stefan | Richardson | 234551738 | 7 | 45000 | XYZ | 24-Oct-60 | |

3 | Elena | Snow | 890376186 | 5 | 20987 | PQR | 02-Feb-80 | Austria |

6 | Mark | Wright | 905684728 | 10 | 100000 | PQR | 10-Jul-55 | Chicago |

SUM Function

SUM function can be used to sum all the numeric values in a column. This function can be used only with columns that have number data type or store numeric data. If there are null values present in the column, then this function will ignore those null values and will do the sum of remaining values.

The syntax of the function is:

SUM( <DISTINCT | ALL> Column)

**Step 1**: In this step we will learn to use the SUM aggregate function.

**Listing 1**: SQL statement to obtain the sum of salaries of all the authors.

SELECT SUM(Salary) FROM Author

When we run the above query, then we will get the sum of salaries of all the authors in a column named SUM(Salary).

We can use DISTINCT keyword to obtain the sum of only distinct values in a column.

The output of the following query is as follows:

Output 1:

SUM(SALARY) 328887

**Figure 1**: Shows the output of listing 1

### AVG Function

AVG function can be used to obtain the average value of all the numeric values in a column. This function can be used only with columns that have number data type or store numeric data. If there are null values present in the column, then this function will ignore those null values and will do the average of remaining values.

The syntax of the function is:

AVG( <DISTINCT | ALL> Column)

**Step 2**: In this step we will learn to use the AVG aggregate function.

**Listing 2**: SQL statement to obtain the average of salaries of all the authors.

SELECT AVG(Salary) FROM Author

When we run the above query, then we will get the average of salaries of all the authors in a column named AVG(Salary).

We can use DISTINCT keyword to obtain the average of only distinct values in a column.

The output of the following query is as follows:

Output 2:

AVG(SALARY)

46983.85714

**Figure 2**: Shows the output of listing 2

### MAX Function

Max function can be used to obtain the maximum value of all the values in a column. This function can be used only with columns that have number data type or character data type or date data type. If there are null values present in the column, then this function will ignore those null values and will give the maximum of remaining values.

The syntax of the function is:

MAX( <DISTINCT | ALL> Column)

**Step 3**: In this step we will learn to use the MAX aggregate function

**Listing 3**: SQL statement to obtain the maximum of first name all the authors(when viewed in alphabetic order).

SELECT MAX(First_Name) FROM Author

When we run the above query, then we will get the maximum of first name of all the authors in a column named MAX(First_Name). The maximum is obtained by viewing the names in alphabetical order.

We can use DISTINCT keyword to obtain the maximum of only distinct values in a column.

The output of the following query is as follows:

Output 3:

MAX(FIRST_NAME)

Steve

**Figure 3**: Shows the output of listing 3

### MIN Function

MIN function can be used to obtain the minimum value of all the values in a column. This function can be used only with columns that have number data type or character data type or date data type. If there are null values present in the column, then this function will ignore those null values and will give the minimum of remaining values.

The syntax of the function is:

MIN( <DISTINCT | ALL> Column)

**Step 4**: In this step we will learn to use the MIN aggregate function.

**Listing 4**: SQL statement to obtain the minimum of date of birth all the authors.

SELECT MIN(DOB) FROM Author

When we run the above query, then we will get the minimum of date of birth of all the authors in a column named MIN(DOB). The minimum is obtained by comparing all the dates and returning the date of birth of the author who is eldest among all.

We can use DISTINCT keyword to obtain the minimum of only distinct values in a column.

The output of the following query is as follows:

Output 4:

MIN(DOB)

30-Jun-50

**Figure 4**: Shows the output of listing 4

### COUNT Function

COUNT function can be used to obtain the count the number of rows that will exist for an expression or column in the table. This function can be used with columns of all data types. Expressions can be used in this function also, but they must not evaluate to null. This function will return the number of rows with non-null values of the expression. Expression can also be a name of the column. ‘*’ can be used to count all the rows including those with null values in them.

The syntax of the function is:

COUNT( < * | DISTINCT | ALL> Expression)

**Step 5**: In this step we will learn to use the COUNT aggregate function.

**Listing 5**: SQL statement to obtain the count of addresses the authors.

SELECT COUNT(Address) FROM Author

When we run the above query, then we will get the count of number of rows having non-null address in them.

We can use DISTINCT keyword to obtain the number of rows with distinct address.

The output of the following query is as follows:Output 5:

COUNT(ADDRESS)

5

**Figure 5**: Shows the output of listing 5

### STDDEV Function

STDDEV function can be used to obtain the standard deviation of the numeric values in column. Standard Deviation is a measure of deviation of values from the mean value. If standard deviation is low then we can assume that the values are closer to the mean. This function can be used with columns of number data types. If there are null values present in the column, then this function will ignore those null values and will give the standard deviation of remaining values.

The syntax of the function is:

STDDEV( < DISTINCT | ALL> Column)

**Step 6**: In this step we will learn to use the STDDEV aggregate function.

**Listing 6**: SQL statement to obtain the standard deviation of books written by the authors.

SELECT STDDEV(Books) FROM Author

When we run the above query, then we will get the standard deviation of books written by authors in a column named ‘STDDEV(Books)’.

We can use DISTINCT keyword to obtain the standard deviation of books with distinct values only.

The output of the following query is as follows:

### Output 6:

STDDEV(BOOKS) 2.299068134

**Figure 6**: Shows the output of listing 6

### VARIANCE Function

VARIANCE function can be used to obtain the variance of the numeric values in column. Variance is a measure variability or difference in the values of a set. This function can be used with columns of number data types. If there are null values present in the column, then this function will ignore those null values and will give the variance of remaining values.

The syntax of the function is:

VARIANCE( < DISTINCT | ALL> Column)

**Step 7**: In this step we will learn to use the VARIANCE aggregate function.

**Listing 7**: SQL statement to obtain the variance of salaries earned by the authors.

SELECT VARIANCE(Salary) FROM Author

When we run the above query, then we will get the variance of salaries earned by authors in a column named ‘VARIANCE(Salary)’.

We can use DISTINCT keyword to obtain the variance of salary with distinct values only.

The output of the following query is as follows:

Output 7:

VARIANCE(SALARY) 1010714057

**Figure 7**: Shows the output of listing 7

Group Functions With DISTINCT Keyword

We can use all group functions with DISTINCT keyword which is used to take into account only those rows which are having distinct values for a column. Duplicate valued rows will be neglected.

**Step 8**: In this step we will learn to use the group functions with DISTINCT keyword.

**Listing 8**: SQL statement to obtain the COUNT of distinct companies for which authors are working.

SELECT COUNT(DISTINCT Company) AS Companies FROM Author

When we run the above query, then we will get the count of distinct companies for which authors are working in a column named ‘Companies’. Note that we have used ‘AS’ keyword to rename the default column name which we would have obtained in result.

The output of the following query is as follows:

Output 8:

COMPANIES

3

**Figure 8**: Shows the output of listing 8

### Conclusion:

In this article, we have learnt about the aggregate group functions. We have learned how we can use them to obtain the various aggregate results like sum, maximum, minimum, average, count, standard deviation and variance of values.

See you next time!