Unlike the character or numeric functions, which act on a single row, aggregate functions act on an entire column of data. Aggregate functions save the developer from having to write a lot of code to determine the maximum column value in a set of records or an average, for example. A single result row is returned by aggregate functions based on the group of rows. Table 2-5 illustrates the more commonly used aggregate functions but is only a partial list. As simple as these are, we're sure you'll agree that they are indeed quite powerful.
|
Function |
Action |
Example |
Displays |
|
count(expr) |
Returns a count of non-null column values for each row retrieved. |
select count(cust_id)
from customers
where cust_state_
province = 'NY'; |
694 |
|
avg(expr) |
Returns the average for the column values and rows selected. |
select avg(amount_sold)
from sales
where prod_id = 117; |
9.92712978 |
|
sum(expr) |
Returns the sum of the column values for all the retrieved rows. |
select sum(amount_sold)
from sales
where prod_id = 117; |
170270.13 |
|
min(expr) |
Returns the minimum value for the column and rows retrieved. |
select min(prod_list_price) from products; |
6.99 |
|
max(expr) |
Returns the maximum value for the column and rows retrieved. |
select max(prod_list_price) from products; |
1299.99 |
|
TABLE 2-5. Common Aggregate Functions |
