AGGREGATE FUNCTIONS

Aggregate functions are 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, for 1997
  

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

The standard aggregate functions are MIN, MAX, AVG, SUM, and COUNT.

They are almost always 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

The GROUP BY clause must include all columns in the SELECT clause except the calculated aggregate columns.  Grouping occurs from left to right, as written in the GROUP BY clause, and groups "break" (to a new group) as soon as a different column value appears in the left-to-right order.    For example, in the last query above, note the break between Chai and Chang, and the individual breaks for each quarter.  Scrolling down in the query, there is a break (a new group or subset) for each product category.

• What would you expect to see if you used an aggregate function in a query but did not include a GROUP BY clause?

Note on aggregate functions and nulls: The aggregate functions do not include rows that have null values in the columns involved in the calculations; 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 rows where the value of column_name is not null, but

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