Aggregate Functions

An aggregate function returns one value for a set of queried rows. The aggregate functions take on values that depend on the set of rows that the WHERE clause of the SELECT statement returns. In the absence of a WHERE clause, the aggregate functions take on values that depend on all the rows that the FROM clause forms.

You cannot use aggregate functions for expressions that contain the following data types:

Aggregates are often used to summarize information about groups of rows in a table. This use is discussed in Composing Advanced SELECT Statements. When you apply an aggregate function to an entire table, the result contains a single row that summarizes all the selected rows.

Using the COUNT Function

Figure 162. Query

SELECT COUNT(*) FROM stock

Figure 163. Query Result

(count(*))

   73

Figure 164 includes a WHERE clause to count specific rows in the stock table, in this case, only those rows that have a manu_code of SHM.

Figure 164. Query

SELECT COUNT (*) FROM stock WHERE manu_code = 'SHM'

Figure 165. Query Result

(count(*))

   17

By including the keyword DISTINCT (or its synonym UNIQUE) and a column name in Figure 166, you can tally the number of different manufacturer codes in the stock table.

Figure 166. Query

SELECT COUNT (DISTINCT manu_code) FROM stock

Figure 167. Query Result

(count)

   9

Using the AVG Function

Figure 168. Query

SELECT AVG (unit_price) FROM stock

Figure 169. Query Result

  (avg)

$197.14

Figure 170 computes the average unit_price of just those rows in the stock table that have a manu_code of SHM.

Figure 170. Query

SELECT AVG (unit_price) FROM stock WHERE manu_code = 'SHM'

Figure 171. Query Result

  (avg)

$204.93

Using the MAX and MIN Functions

You can combine aggregate functions in the same SELECT statement. For example, you can include both the MAX and the MIN functions in the select list, as Figure 172 shows.

Figure 172. Query

SELECT MAX (ship_charge), MIN (ship_charge) FROM orders

Figure 172 finds and displays both the highest and lowest ship_charge in the orders table, as Figure 173 shows.

Figure 173. Query Result

 (max)     (min)
 
$25.20     $5.00

Using the SUM Function

Figure 174 calculates the total ship_weight of orders that were shipped on July 13, 1998.

Figure 174.

SELECT SUM (ship_weight) FROM orders
   WHERE ship_date = '07/13/1998'

Figure 175. Query Result

(sum)

130.5

Using the RANGE Function

The RANGE function computes the difference between the maximum and the minimum values for the selected rows.

You can apply the RANGE function only to numeric columns. Figure 176 finds the range of prices for items in the stock table.

Figure 176. Query

SELECT RANGE(unit_price) FROM stock

Figure 177. Query Result

(range)

955.50

As with other aggregates, the RANGE function applies to the rows of a group when the query includes a GROUP BY clause, which Figure 178 shows.

Figure 178. Query

SELECT RANGE(unit_price) FROM stock
   GROUP BY manu_code

Figure 179. Query Result

(range)

820.20
595.50
720.00
225.00
632.50
  0.00
460.00
645.90
425.00

Using the STDEV Function

The STDEV function computes the standard deviation for the selected rows. It is the square root of the VARIANCE function.

You can apply the STDEV function only to numeric columns. The following query finds the standard deviation on a population:

As with the other aggregates, the STDEV function applies to the rows of a group when the query includes a GROUP BY clause, as the following example shows:

Nulls are ignored unless every value in the specified column is null. If every column value is null, the STDEV function returns a null for that column. For more information about the STDEV function, see the Expression segment in the IBM Informix: Guide to SQL Syntax.

Using the VARIANCE Function

The VARIANCE function returns the variance for a sample of values as an unbiased estimate of the variance for all rows selected. It computes the following value:

In this example, Xi is each value in the column and N is the total number of values in the column. You can apply the VARIANCE function only to numeric columns. The following query finds the variance on a population:

As with the other aggregates, the VARIANCE function applies to the rows of a group when the query includes a GROUP BY clause, which the following example shows:

Nulls are ignored unless every value in the specified column is null. If every column value is null, the VARIANCE function returns a null for that column. For more information about the VARIANCE function, see the Expression segment in the IBM Informix: Guide to SQL Syntax.

Applying Functions to Expressions

Figure 180 shows how you can apply functions to expressions and supply display labels for their results.

Figure 180. Query

SELECT MAX (res_dtime - call_dtime) maximum,
   MIN (res_dtime - call_dtime) minimum,
   AVG (res_dtime - call_dtime) average
   FROM cust_calls

Figure 180 finds and displays the maximum, minimum, and average amounts of time (in days, hours, and minutes) between the reception and resolution of a customer call and labels the derived values appropriately. Figure 181 shows these qualities of time.

Figure 181. Query Result

maximum         minimum         average

5 20:55         0 00:01         1 02:56