Aggregate Functions

Aggregate functions are SQL functions that take a collection of values as inputs and return a single value. There are five built-in aggregate functions in SQL.

* @@avg()@@ - compute the average of a collection of numeric values. * @@sum()@@ - compute the sum of a collection of numeric values. * @@min()@@ - find the minimum element from a collection of data elements. * @@max()@@ - find the maximum element from a collection of data elements. * @@count()@@ - count the number of items in a collection.

!! Use of Aggregate Functions

When an SQL query is performed, the result is a collection of tuples stored in a table. The attributes comprising the results table depends on the specific query and source tables used with the query. The tuples of the results table are created, one at a time as the tuples in the source table(s) are evaluated. Instead of creating a results table of the selected source tuples, we can pass the values of a specific attribute to an aggregate function. For example, consider the following query on a table in the @@[[CSCI321Fall09/GradebookDatabase | gradebook]]@@ database,

(:source lang=sql:)[@ SELECT * FROM student; @]

which returns all attributes and tuples from the @@student@@ table. Instead of returning the actual tuples, we can pass the tuples to the @@count()@@ function to count the number of rows in the table.

(:source lang=sql:)[@ SELECT count( * ) FROM student; @]

Suppose we want to compute the total number of possible points by summing the value attribute of the @@assignment@@ table

(:source lang=sql:)[@ SELECT sum( value ) FROM assignment; @]

For a more complex example, suppose we want to compute the average earned on assignment #1. We can apply the @@avg()@@ function to the @@points@@ attribute for the rows in the @@grade@@ table where @@asgm_id = 1@@

(:source lang=sql:)[@ SELECT avg( points ) FROM grade WHERE asgm_id = 1; @]

!! Grouping Function Results

(:source lang=sql:)[@ SELECT agm_id, avg( points ) FROM grade GROUP BY asgm_id; @]

!! Renaming the Results

(:source lang=sql:)[@ SELECT asgm_id, avg( points ) as avg FROM grade GROUP BY asgm_id; @]