MySQL® Administrator's Bible

Appendix B. Functions and Operators

IN THIS CHAPTER

• Using aggregation functions

• Using bitwise operators

• Compressing and encrypting data

• Testing, logic, and control flow

• Using server-level functions

• Working with data types

This appendix contains the functions and operators available in MySQL, including whether they are an SQL Standard or a MySQL Extension.

Using Aggregation Functions

Aggregation functions take a single expression as an argument but may take input from many rows; they are row-wise functions. For example, the AVG(expr) function takes a simple average (the sum divided by the count):

mysql> CREATE TABLE nums (num TINYINT);
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO nums (num) VALUES (1),(2),(3);
Query OK, 3 rows affected (0.48 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select AVG(num) FROM nums;
+----------+
| AVG(num) |
+----------+
| 2.0000   |
+----------+
1 row in set (0.03 sec)

Although aggregate functions work on expressions that encompass one or more rows, they only take one expression as an argument. For example, the syntax of AVG(expr) does not allow for the following:

mysql> SELECT AVG(1,2,3);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2,3)' at line 1

Warning

One exception to this rule is that COUNT(DISTINCT expr) can take multiple arguments.

When expr evaluates as NULL, for example when there are no rows that match, most aggregation functions return ...