DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH PRINT BOOK
 

(mysql.info) group-by-functions

Info Catalog (mysql.info) group-by-functions-and-modifiers (mysql.info) group-by-functions-and-modifiers (mysql.info) group-by-modifiers
 
 12.10.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 as of MySQL 5.0.3 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.
 
      Beginning with MySQL 5.0.19, the type returned by `GROUP_CONCAT()'
      is always `VARCHAR' unless `group_concat_max_len' is greater than
      512, in which case, it returns a `BLOB'.  (Previously, it returned
      a `BLOB' with `group_concat_max_len' greater than 512 only if the
      query included an `ORDER BY' clause.)
 
      See also `CONCAT()' and `CONCAT_WS()':  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  mysql-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. As of MySQL 5.0.3, 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()'). This function was added in MySQL 5.0.3. Before
      5.0.3, you can 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()'. This function was added in MySQL 5.0.3.
 
      `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.0 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. This function was added in MySQL
      5.0.3. Before 5.0.3, you can 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. This function was added in MySQL
      5.0.3.
 
      `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. As of MySQL 5.0.3, the standard SQL
      function `VAR_POP()' can be used instead.
 
      `VARIANCE()' returns `NULL' if there were no matching rows.
 
Info Catalog (mysql.info) group-by-functions-and-modifiers (mysql.info) group-by-functions-and-modifiers (mysql.info) group-by-modifiers
automatically generated byinfo2html