Aggregate Functions

Nested Queries

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 gradebook database,

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.

SELECT COUNT( * ) FROM student;

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

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

SELECT avg( points ) FROM grade WHERE asgm_id = 1;

Grouping Function Results

SELECT agm_id, avg( points ) FROM grade GROUP BY asgm_id;

Renaming the Results

SELECT asgm_id, avg( points ) AS avg FROM grade GROUP BY asgm_id;

Set Operations

SQL Reference

Nested Queries