Java™ Oracle® Database Development

Aggregate Functions, GROUP BY and HAVING

Aggregate functions are functions that take a set of values—a column or an expression including a column name—in a result set and return a single value. For example, we can query a table that contains students' test scores to find the average by using the AVG() function. In SQL, the basic aggregate functions are:

• AVG()— Calculates average

• COUNT()— Returns the number of rows

• MAX()— The highest value in the set of values

• MIN()— The smallest value in the set

• SUM()— The sum of the values

When we use an aggregate function in a select list, we can't use anything else but aggregate functions in the select list—unless we use a GROUP BY clause.

In the previous chapter, we considered a table of fruit.

 Fruit Color ...