Managing & Using MySQL, 2nd Edition

Aggregate Functions

Aggregate functions operate on a set of data. These are usually used to perform some action on a complete set of returned rows. For example, SELECT AVG(height) FROM kids would return the average of all the values of the height field in the kids table.

AVG( expression )

Returns the average value of the values in expression (e.g., SELECT AVG(score) FROM tests).

BIT_AND( expression )

Returns the bitwise AND aggregate of all the values in expression (e.g., SELECT BIT_AND(flags) FROM options). A bit will be set in the result if and only if the bit is set in every input field.

BIT_OR( expression )

Returns the bitwise OR aggregate of all the values in expression (e.g., SELECT BIT_OR(flags) FROM options). A bit is set in the result if it is set in at least one of the input fields.

COUNT( expression )

Returns the number of times expression was not null. COUNT(*) will return the number of rows with some data in the entire table (e.g., SELECT COUNT(*) FROM folders).

MAX( expression )

Returns the largest value in expression (e.g., SELECT MAX (elevation) FROM mountains).

MIN( expression )

Returns the smallest value in expression (e.g., SELECT MIN(level) FROM toxic_waste).

STD( expression )/STDDEV( expression )

Returns the standard deviation of the values in expression (e.g., SELECT STDDEV(points) FROM data).

SUM( expression )

Returns the sum of the values in expression (e.g., SELECT SUM(calories) FROM daily_diet).