
AVG([DISTINCT]
expr
)
Returns the average value of
expr
. The
DISTINCT
option can be used to return the
average of the distinct values of
expr
.
AVG()
returns NULL
if
there were no matching rows.
mysql> SELECT student_name, AVG(test_score)
> FROM student
> GROUP BY student_name;

BIT_AND(expr
)
Returns the bitwise AND
of all bits in
expr
. The calculation is
performed with 64bit (BIGINT
) precision.
This function returns
18446744073709551615
if there were no
matching rows. (This is the value of an unsigned
BIGINT
value with all bits set to 1.)

BIT_OR(expr
)
Returns the bitwise OR
of all bits in
expr
. The calculation is
performed with 64bit (BIGINT
) precision.
This function returns 0
if there were no
matching rows.

BIT_XOR(expr
)
Returns the bitwise XOR
of all bits in
expr
. The calculation is
performed with 64bit (BIGINT
) precision.
This function returns 0
if there were no
matching rows.

COUNT(expr
)
Returns a count of the number of nonNULL
values in the rows retrieved by a SELECT
statement.
COUNT()
returns 0
if
there were no matching rows.
mysql> SELECT student.student_name,COUNT(*)
> FROM student,course
> WHERE student.student_id=course.student_id
> GROUP BY student_name;
COUNT(*)
is somewhat different in that it
returns a count of the number of rows retrieved, whether or
not they contain NULL
values.
COUNT(*)
is optimized to return very
quickly if the SELECT
retrieves from one
table, no other columns are retrieved, and there is no
WHERE
clause. For example:
mysql> SELECT COUNT(*) FROM student;
This optimization applies only to MyISAM
tables only, because an exact row count is stored for this
storage engine and can be accessed very quickly. For
transactional storage engines such as
InnoDB
and BDB
,
storing an exact row count is more problematic because
multiple transactions may be occurring, each of which may
affect the count.

COUNT(DISTINCT
expr
,[expr
...])
Returns a count of the number of different
nonNULL
values.
COUNT(DISTINCT)
returns
0
if there were no matching rows.
mysql> SELECT COUNT(DISTINCT results) FROM student;
In MySQL, you can obtain the number of distinct expression
combinations that do not contain NULL
by
giving a list of expressions. In standard SQL, you would
have to do a concatenation of all expressions inside
COUNT(DISTINCT ...)
.

GROUP_CONCAT(expr
)
This function returns a string result with the concatenated
nonNULL
values from a group. It returns
NULL
if there are no
nonNULL
values. The full syntax is as
follows:
GROUP_CONCAT([DISTINCT] expr
[,expr
...]
[ORDER BY {unsigned_integer
 col_name
 expr
}
[ASC  DESC] [,col_name
...]]
[SEPARATOR str_val
])
mysql> SELECT student_name,
> GROUP_CONCAT(test_score)
> FROM student
> GROUP BY student_name;
Or:
mysql> SELECT student_name,
> GROUP_CONCAT(DISTINCT test_score
> ORDER BY test_score DESC SEPARATOR ' ')
> FROM student
> GROUP BY student_name;
In MySQL, you can get the concatenated values of expression
combinations. You can eliminate duplicate values by using
DISTINCT
. If you want to sort values in
the result, you should use ORDER BY
clause. To sort in reverse order, add the
DESC
(descending) keyword to the name of
the column you are sorting by in the ORDER
BY
clause. The default is ascending order; this
may be specified explicitly using the ASC
keyword. SEPARATOR
is followed by the
string value that should be inserted between values of
result. The default is a comma
(‘,
’). You can eliminate the
separator altogether by specifying SEPARATOR
''
.
You can set a maximum allowed length with the
group_concat_max_len
system variable.
(The default value is 1024.) The syntax to do this at
runtime is as follows, where val
is an unsigned integer:
SET [SESSION  GLOBAL] group_concat_max_len = val
;
If a maximum length has been set, the result is truncated to
this maximum length.
See also CONCAT()
and
CONCAT_WS()
:
Section 12.3, “String Functions”.

MIN([DISTINCT]
expr
)
,
MAX([DISTINCT]
expr
)
Returns the minimum or maximum value of
expr
. MIN()
and MAX()
may take a string argument; in
such cases they return the minimum or maximum string value.
See Section 7.4.5, “How MySQL Uses Indexes”. The
DISTINCT
keyword can be used to find the
minimum or maximum of the distinct values of
expr
, however, this produces the
same result as omitting DISTINCT
.
MIN()
and MAX()
return
NULL
if there were no matching rows.
mysql> SELECT student_name, MIN(test_score), MAX(test_score)
> FROM student
> GROUP BY student_name;
For MIN()
, MAX()
, and
other aggregate functions, MySQL currently compares
ENUM
and SET
columns
by their string value rather than by the string's relative
position in the set. This differs from how ORDER
BY
compares them. This is expected to be rectified
in a future MySQL release.

STD(expr
)
STDDEV(expr
)
Returns the population standard deviation of
expr
. This is an extension to
standard SQL. The STDDEV()
form of this
function is provided for compatibility with Oracle. The
standard SQL function STDDEV_POP()
can be
used instead.
These functions return NULL
if there were
no matching rows.

STDDEV_POP(expr
)
Returns the population standard deviation of
expr
(the square root of
VAR_POP()
). You can also use
STD()
or STDDEV()
,
which are equivalent but not standard SQL.
STDDEV_POP()
returns
NULL
if there were no matching rows.

STDDEV_SAMP(expr
)
Returns the sample standard deviation of
expr
(the square root of
VAR_SAMP()
.
STDDEV_SAMP()
returns
NULL
if there were no matching rows.

SUM([DISTINCT]
expr
)
Returns the sum of expr
. If the
return set has no rows, SUM()
returns
NULL
. The DISTINCT
keyword can be used in MySQL 5.1 to sum only
the distinct values of expr
.
SUM()
returns NULL
if
there were no matching rows.

VAR_POP(expr
)
Returns the population standard variance of
expr
. It considers rows as the
whole population, not as a sample, so it has the number of
rows as the denominator. You can also use
VARIANCE()
, which is equivalent but is
not standard SQL.
VAR_POP()
returns NULL
if there were no matching rows.

VAR_SAMP(expr
)
Returns the sample variance of
expr
. That is, the denominator is
the number of rows minus one.
VAR_SAMP()
returns
NULL
if there were no matching rows.

VARIANCE(expr
)
Returns the population standard variance of
expr
. This is an extension to
standard SQL. The standard SQL function
VAR_POP()
can be used instead.
VARIANCE()
returns
NULL
if there were no matching rows.