Using Aggregate Functions

The functions you ve seen up to now operate on a single row at a time and return one row of output for each input row. In this section, you ll learn how about aggregate functions, which operate on a group of rows at the same time and return one row of output for each group of rows. Examples of when you want to use an aggregate function include computing the average price of a product and finding the maximum price of a product.

Note  

Aggregate functions are also known as group functions because they operate on groups of rows.

The aggregate functions are mainly numerical, and you typically use them to return a value based on a set of values contained in columns of rows. As you ll see shortly, some of the aggregate functions allow you to perform sophisticated statistical computations . Table 3-7 lists some of the aggregate functions. These functions all return a NUMBER .

Table 3-7: Aggregate Functions

Function	Description
AVG( x )	Returns the average value of x .
COUNT( x )	Returns the number of rows returned by a query involving x .
MAX(x)	Returns the maximum value of x .
MEDIAN( x )	Returns the median value of x .
MIN( x )	Returns the minimum value of x .
STDDEV( x )	Returns the standard deviation of x .
SUM( x )	Returns the sum of x .
VARIANCE( x )	Returns the variance of x .

Here are some points to remember when using aggregate functions:

• You can use the COUNT() , MAX() , and MIN() functions with numbers , strings, and datetimes.

• Null values are ignored by aggregate functions. This is because a null value indicates the value is unknown and is therefore not applicable to the aggregate function s calculation.

• You can use the DISTINCT keyword with an aggregate function to exclude duplicate entries from the aggregate function s calculation.

You ll learn more about some of the aggregate functions shown in Table 3-7 in the following sections.

AVG()

You use AVG( x ) to get the average value of x . The following example gets the average price of the products; notice that the price column from the products table is passed to the AVG() function:

  SELECT AVG(price)   FROM products;  AVG(PRICE) ---------- 19.7308333 

You can use the aggregate functions with any valid expression. For example, the following query passes the expression price + 2 to AVG(); this adds 2 to each row s price column value and then returns the average of those values.

  SELECT AVG(price + 2)   FROM products;  AVG(PRICE) ---------- 21.7308333 

You can use the DISTINCT keyword to exclude identical values from a group computation. For example, the following query uses the DISTICT keyword to exclude identical values in the price column when computing the average using AVG() :

  SELECT AVG(DISTINCT price)   FROM products;  AVG(DISTINCTPRICE) ------------------   20.2981818 

Notice the average in this example value is slightly different from the average shown in the first example of this section, which didn t include the DISTINCT keyword. The average values are different because the price column contains two values that are the same: 13.49 for products #7 and #12. The price column value for product #12 is considered a duplicate and is excluded from the computation performed by AVG() ”and this results in the average being different.

COUNT()

You use COUNT( x ) to get the number of rows returned by a query. The following example gets the number of rows in the products table using COUNT() :

  SELECT COUNT(product_id)   FROM products;  COUNT(PRODUCT_ID) -----------------      12 

Tip  

You should avoid using the asterisk ( * ) with the COUNT() function as it may take longer for COUNT() to return the result. Instead, you should use a column in the table or use the ROWID column. As you saw in the previous chapter, the ROWID column contains the internal location of the row in the Oracle database.

The following example passes ROWID to COUNT() and gets the number of rows in the products table:

  SELECT COUNT(ROWID)   FROM products;  COUNT(ROWID) ------------   12 

MAX() and MIN()

You use MAX( x ) and MIN( x ) to get the maximum and minimum values for x . The following example displays the maximum and minimum values of the price column from the products table using MAX() and MIN() :

  SELECT MAX(price), MIN(price)   FROM products;  MAX(PRICE) MIN(PRICE) ---------- ----------  49.99  10.99 

You may use MAX() and MIN() with any type, including strings and dates. When you use MAX() with strings, the strings are ordered alphabetically with the maximum string being at the bottom of a list and the minimum string being at the top of the list. For example, the string Albert would appear before Zeb in such a list. The following example gets the maximum and minimum name strings from the products table using MAX() and MIN() :

  SELECT MAX(name), MIN(name)   FROM products;  MAX(NAME)       MIN(NAME) ------------------------------ ------------------------------ Z Files    2412: The Return 

In the case of dates, the maximum date occurs at the latest point, and the minimum date at the earliest point. The next example displays the maximum and minimum dob from the customers table using MAX() and MIN() :

  SELECT MAX(dob), MIN(dob)   FROM customers;  MAX(DOB) MIN(DOB) --------- --------- 16-MAR-71 01-JAN-65 

STDDEV()

You use STDDEV( x ) to get the standard deviation of x . Standard deviation is a statistical function, and is defined as being the square root of the variance (you ll learn about variance shortly).

The following example displays the standard deviation of the price column values from the products table using STDDEV() :

  SELECT STDDEV(price)   FROM products;  STDDEV(PRICE) -------------  11.0896303 

SUM()

SUM( x ) adds all the values in x and returns the total. The following example displays the sum of the price column from the products table using SUM() :

  SELECT SUM(price)   FROM products;  SUM(PRICE) ----------  236.77 

VARIANCE()

You use VARIANCE( x ) to get the variance of x . Variance is a statistical function and is defined as the spread or variation of a group of numbers in a sample, equal to the square of the standard deviation.

The following example gets the variance of the price column values from the products table using VARIANCE() :

  SELECT VARIANCE(price)   FROM products;  VARIANCE(PRICE) ---------------   122.979899