                On-line Guides All Guides eBook Store iOS / Android Linux for Beginners Office Productivity Linux Installation Linux Security Linux Utilities Linux Virtualization Linux Kernel System/Network Admin Programming Scripting Languages Development Tools Web Development GUI Toolkits/Desktop Databases Mail Systems openSolaris Eclipse Documentation Techotopia.com Virtuatopia.com How To Guides Virtualization General System Admin Linux Security Linux Filesystems Web Servers Graphics & Desktop PC Hardware Windows Problem Solutions Privacy Policy  ### 12.11.1. `GROUP BY` (Aggregate) Functions

This section describes group (aggregate) functions that operate on sets of values. Unless otherwise stated, group functions ignore `NULL` values.

If you use a group function in a statement containing no `GROUP BY` clause, it is equivalent to grouping on all rows.

The `SUM()` and `AVG()` aggregate functions do not work with temporal values. (They convert the values to numbers, which loses the part after the first non-numeric character.) To work around this problem, you can convert to numeric units, perform the aggregate operation, and convert back to a temporal value. Examples:

```SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(`time_col`))) FROM `tbl_name`;
SELECT FROM_DAYS(SUM(TO_DAYS(`date_col`))) FROM `tbl_name`;
```
• ```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 64-bit (`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 64-bit (`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 64-bit (`BIGINT`) precision.

This function returns `0` if there were no matching rows.

• `COUNT(expr)`

Returns a count of the number of non-`NULL` 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 non-`NULL` 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 non-`NULL` values from a group. It returns `NULL` if there are no non-`NULL` 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. Published under the terms of the GNU General Public License Design by Interspire