AGGREGATE FUNCTIONS

A series of straightforward math or statistical functions that apply to sets or subsets of data rather than to individual instances (rows).

Examples

Order Subtotals: The (discounted) total $ amount for all items in each order

Sales By Category: The (discounted) total $ amount for each product, by category, for 1997

Category Sales for 1997 (based on next query): Total sales, by category.

Product Sales for 1997: Total product sales, by quarter.

Standard aggregate functions: MIN, MAX, AVG, SUM, COUNT

Used with GROUP BY to create the subsets

with WHERE before GROUP BY to set criteria before calculating

and HAVING after GROUP BY to set criteria for the calculated results

For example:

In the Category Sales for 1997 query, to see only totals for the first quarter, add the following criteria before the GROUP BY clause (ShippedQuarter is a column in the Product Sales for 1997 query):

WHERE ShippedQuarter like "*1"

In the modified query, to see only totals greater than $20000, add the following criteria after the GROUP BY clause:

HAVING Sum(ProductSales) > 20000

Note: No need to include the table or query name – the ProductSales column only appears in one of the tables or queries.

Note on aggregate functions and nulls: The aggregate functions do not include rows that have null values, that is, nulls are not handled as if they were zero. The only function that can return a value for all rows, regardless of the presence of nulls is the COUNT function:

E.g. COUNT(column_name) returns a count of all non-null rows, but

COUNT(*) returns the count of all rows, even those with nulls