Chapter 15: Using the Built-In Functions in Queries

Aggregate functions

The aggregate functions generate summary values that appear as new columns in the query results. Table 15-6 lists the aggregate functions, their arguments, and the results they return.

**Table 15-6: Aggregate functions**
Function	Argument	Result
avg	(all \| distinct) expression	Returns the numeric average of all (distinct) values
count	(all \| distinct) expression	Returns the number as an integer of (distinct) non-null values or the number of rows
count_big	(all \| distinct) expression	Returns the number as a bigint of (distinct) non-null values or the number of rows
max	(expression)	Returns the highest value in an expression
min	(expression)	Returns the lowest value in a column
sum	(all \| distinct) expression	Returns the total of the values

Examples are as follows:

select avg(advance), sum(total_sales) 
from titles 
where type = "business"

------------------------ -----------
                 6281.25      30,788
 
(1 row affected)

select count(distinct city) from authors

-----------
         16
 
(1 row affected)

select discount from salesdetail
compute max(discount)

discount            
--------------------
           40.000000
           ...
           46.700000
 
Compute Result:
--------------------
           62.200000
 
(117 rows affect)

select min(au_lname) from authors

---------------------
Bennet
 
(1 row affected)

View this book as PDF