Summary

This chapter introduced nine aggregate functions that can be used in a simple SELECT statement to return summary values for the entire range or with the GROUP BY clause to roll up groups of rows with similar values. The aggregate functions include simple mathematical operations, such as Count and Sum, and statistical functions such as variance and standard deviation.

The GROUP BY clause can be used to reduce the results of a query to distinct combinations of grouped values. When used with aggregate functions, this produces value summaries within the grouping.

The ROLLUP and CUBE statements extend grouping functionality by adding summary rows. Adding WITH ROLLUP to a grouped query will produce summary rows for the first column in the GROUP BY list. Adding WITH CUBE will add summary rows for every possible combination of grouped column values. The GROUPING() function can be used along with these operators to flag summary rows and to avoid confusion.

Use the COMPUTE statement sparingly and only for quick reports in Query Analyzer or the Query Editor. Although it's simple compared to using some of the other techniques discussed in this chapter, it is not ANSI SQL compliant and doesn't work with most software and programming tools. It is, however, a convenient method for viewing summary information quickly.