Built-in Functions

Aggregate functions

Aggregate functions perform a calculation on a set of values and return a single, or summary, value. Table 16.4 lists these functions.

FUNCTION DESCRIPTION
AVG Returns the average of all the values, or only the DISTINCT values, in the expression.
COUNT Returns the number of non-null values in the expression. When DISTINCT is specified, COUNT finds the number of unique non-null values.
COUNT(*) Returns the number of rows. COUNT(*) takes no parameters and cannot be used with DISTINCT.
MAX Returns the maximum value in the expression. MAX can be used with numeric, character, and datetime columns, but not with bit columns. With character columns, MAX finds the highest value in the collating sequence. MAX ignores any null values.
MIN Returns the minimum value in the expression. MIN can be used with numeric, character, and datetime columns, but not with bit columns. With character columns, MIN finds the value that is lowest in the sort sequence. MIN ignores any null values.
SUM Returns the sum of all the values, or only the DISTINCT values, in the expression. SUM can be used with numeric columns only.


Table 16.4 A list of aggregate functions and descriptions.

Below are examples of each of the aggregate functions listed in Table 16.4.

Example #1:  AVG

SELECT AVG (price) AS 'Average Title Price'
FROM Books

Example #2: COUNT

SELECT COUNT(PubID) AS 'Number of Publishers'
FROM Publishers

Example #3: COUNT

SELECT COUNT(province) AS 'Number of Publishers'
FROM Publishers

Example #3: COUNT (*)

SELECT COUNT(*)
FROM Employees
WHERE job_lvl = 35

Example #4: MAX

SELECT MAX (HireDate)
FROM Employees

Example #5: MIN

SELECT MIN (price)
FROM Books

Example #6: SUM

SELECT SUM(discount) AS 'Total Discounts'
FROM Discounts