MySQL in a Nutshell, 2nd Edition

Chapter 10. Aggregate Clauses, Aggregate Functions, and Subqueries

MySQL has many built-in functions that you can use in SQL statements for performing calculations on combinations of values in databases; these are called aggregate functions. They include such types of basic statistical analysis as counting rows, determining the average of a given column’s value, finding the standard deviation, and so forth. The first section of this chapter describes MySQL aggregate functions and includes examples of most of them. The second section provides a tutorial about subqueries. It includes several examples of subqueries in addition to the ones shown in the first section and in various examples throughout this book. Subqueries are included in this chapter because they are often used with GROUP BY and aggregate functions and because they’re another method for grouping selected data.

The following functions are covered in this chapter:

AVG(), BIT_AND(), BIT_OR(), COUNT(), GROUP_CONCAT(), MAX(), MIN(), STD(), STDDEV(), STDDEV_POP(), STDDEV_SAMP(), SUM(), VAR_POP(), VAR_SAMP(), VARIANCE().

Aggregate Functions in Alphabetical Order

This section describes each aggregate function. Many of the examples use a subquery. For detailed information about subqueries, see the Subqueries” section later in this chapter.

A few general aspects of aggregate functions include:

• Aggregate functions return NULL when they encounter an error.

• Most uses for aggregate functions include a GROUP BY clause, which is specified ...