Oracle Certification - SQL Fundamentals I - Group Functions

Group Functions

Group functions execute once per group of rows, and return a single result per group. So for example the EMPLOYEES table can be divided by the column DEPARTMENT_ID, and a group function can return a result per DEPARTMENT_ID.

Apart from COUNT(*) group functions ignore NULLs, this can avoided by using NVL. The default behaviour is the count ALL rows including duplicates, this can be overridden with DISTINCT.

If only constant values and group functions are used then no GROUP BY clause is required, and the whole result is the order in which the results are grouped. If a non-constant value is in the SELECT clause along with a group function then it must be included in a GROUP BY clause, otherwise an error will be returned.

The GROUP BY clause will affect how to results are grouped together by Oracle, they will still be returned un-sorted unless an ORDER BY clause is included.

COUNT({*|[DISTINCT|ALL] expr})

The COUNT function counts the number of rows in a group. COUNT(*) counts all rows in the group including duplicates and nulls. COUNT(DISTINCT expr) counts on unique occurrences of expr. COUNT(ALL expr) and COUNT(expr) are equivalent, and they count the non-null occurrences of expr.  The value of expr can be NUMBER, DATE, CHAR or VARCHAR2.

Note: COUNT(*) will count the number of rows including NULLs, COUNT(expr) will count only non-NULL values of that column.

AVG([DISTINCT|ALL] expr)

AVG calculates the average of a group of numeric values. AVG(DISTINCT expr) divides the sum of the distinct values of expr, divided by the number of distinct values of expr per group. AVG(ALL expr) and AVG(expr) calculates the sum of the non-null values of expr, divided by the number of non-null occurrences of expr. Expr should be a NUMBER.

SUM([DISTINCT|ALL] expr)

SUM(DISTINCT expr) adds all unique values of expr in the group. SUM(expr) and SUM(ALL expr) adds all non-null values of expr in the group. NULL values are ignored, and expr is expected to be a NUMBER.

MAX([DISTINCT|ALL] expr) MIN([DISTINCT|ALL] expr)

MAX / MIN return the maximum / minimum values of expr in the group. NULL values are ignored, and expr can be NUMBER, DATE, CHAR or VARCHAR2.

Note: With the exception of COUNT(*) group functions ignore NULL values.

Nesting

Single row functions can be nested to any level, but group functions can only be nested to at most two levels deep. So COUNT(AVG(expr)) is valid, but COUNT(AVG(MIN(expr))) is not.

Grouping by Multiple Columns

In the following SQL statement the sum of the salaries has been group by first DEPARTMENT_ID and then MANAGER_ID. This results in a SUM of the salaries for each employee grouped first by DEPARTMENT_ID and then by MANAGER_ID:

The HAVING Clause

The HAVING clause works in much the same way as the WHERE clause apart from the restriction is based on the results of group functions.

Obviously the HAVING clause can only be used when there is a GROUP BY clause as well.

Note: the HAVING clause can occur before the GROUP BY clause, but it is more common for it to occur after it. A favorite question in the exam is to put a group function in the WHERE clause, which causes an error.

Summary

General Aggregation

• Group functions execute once per group of data
• The GROUP BY clause defines which column(s) are grouped together
• GROUP BY follows the WHERE clause, and precedes the ORDER BY clause
• Group functions return a single result per group
• Group functions can be nested at most two levels deep
• Group functions cannot appear in the WHERE clause
• The HAVING clause is used to filter using the results of group functions

Group Functions

• COUNT returns the number of rows in a group
• Uniquely COUNT(*) includes NULLs and duplicates
• By default ALL is implied, i.e. non-null values including duplicates
• SUM returns the total of all the non-null numeric expressions in a group
• AVG divides the sum of a group by the number of non-null rows in that group
• MAX/MIN return the maximum/minimum non-null value in a group

These group functions will be in the exam for sure, and where NULLs are excluded or not, nesting and so on will all be tested.

The next topic is going to be Joining tables.